I am looking to create a string natively in sqlite3 that will gather all non-numeric values, however the column format has all data in TEXT format and wrapped with [""] such as:
["1234567"] or ["word"]
there is no mix of numbers and letters, but I am unable to build a string that parses just the non-numeric values within sqlite itself. Closest I have requires sed and grep after (novice at best with any language and am learning daily) which would result in 'word', with the above examples as values:
sqlite3 data.db "select DISTINCT column from table ORDER BY column" |sed 's/[][]//g'|sed 's/"//g' | grep -v '[0-9]'
word
CodePudding user response:
Since there is no mix of numbers and letters you can get all the rows with non-numeric chars with the operator GLOB
:
SELECT *
FROM tablename
WHERE col NOT GLOB '*[0-9]*';
See a simplified demo.