CODEWITHSUNDEEP
sqlsqlite
James Raitsev
James Raitsev

Reputation: 96411

How to select all values that a not a number using SQL?

Due to an earlier error, I ended up with letters and symbols in places where I should have had integers and floars. At this time, I don't know the extent of the problem and working to correct the code as we move forward.

As of right now when I run SELECT distinct col1 from table; I get integers, floats, symbols and letters. A few million of them.

How can I update the SQL to exclude all numbers? In other words, show me only letters and symbols.

Upvotes: 0

Views: 799

Answers (2)

forpas
forpas

Reputation: 164099

You can use the GLOB operator:

select col1 
from tablename 
where col1 GLOB '*[^0-9]*'

This will return all values of col1 that contain any character different than a digit.
You may change it to include '.' in the list of chars:

where col1 GLOB '*[^0-9.]*'

See the demo.

If what you want is values that do not contain any digits then use this:

select col1 
from tablename 
where col1 not GLOB '*[0-9]*'

See the demo.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269953

Hmmm . . . SQLite doesn't have regular expressions built-in, making this a bit of a pain. If the column actually contains numbers and strings (because that is possible in SQLite), you can use

where typeof(col) = 'text'

If the types are all text (so '1.23' rather than 1.23), then this may do what you want:

where cast( (col + 0) as text) = col

Upvotes: 1

Related Questions