Home > OS >  removing special characters natively in sqlite
removing special characters natively in sqlite

Time:12-21

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.

  • Related