Return rows where first character is non-alpha

sql sqlite sql-like alphanumeric
XSL picture XSL · Jun 25, 2012 · Viewed 21.6k times · Source

I'm trying to retrieve all columns that start with any non alpha characters in SQlite but can't seem to get it working. I've currently got this code, but it returns every row:

SELECT * FROM TestTable WHERE TestNames NOT LIKE '[A-z]%'

Is there a way to retrieve all rows where the first character of TestNames are not part of the alphabet?

Answer

Twelfth picture Twelfth · Jun 25, 2012

Are you going first character only?

select * from TestTable WHERE substr(TestNames,1) NOT LIKE '%[^a-zA-Z]%'

The substr function (can also be called as left() in some SQL languages) will help isolate the first char in the string for you.

edit: Maybe substr(TestNames,1,1) in sqllite, I don't have a ready instance to test the syntax there on.

Added:

select * from TestTable WHERE Upper(substr(TestNames,1,1)) NOT in ('A','B','C','D','E',....)

Doesn't seem optimal, but functionally will work. Unsure what char commands there are to do a range of letters in SQLlite.

I used 'upper' to make it so you don't need to do lower case letters in the not in statement...kinda hope SQLlite knows what that is.

Related questions

SQLite Like % and _

I can't figure out what the underscore character does in an SQLite like statement. The wildcard character, %, is probably the same as in most other SQL databases. So, what does the _ character do?

Read More
SQLite query, 'LIKE'

I am trying to retrieve information from my database. I have words like Lim Won Mong and Limited. If I do my query, SELECT name FROM memberdb WHERE name LIKE '%LIM%', it displays both Lim Won Mong and …

Read More
Match only entire words with LIKE?

So 'awesome document' LIKE '%doc%' is true, because doc is a sub-string. But, I want it to be false while 'awesome doc' or 'doc awesome' or 'awesome doc awesome' should be true. How can I do with with …

Read More