Returns lines where the first character is not alpha

Question

I am trying to get all columns that start with any alpha characters in SQlite but cannot make it work. Currently, I got this code, but it returns every line:

SELECT * FROM TestTable WHERE TestNames NOT LIKE '[Az]%' 

Is there a way to get all the strings where the first character of TestNames is not part of the alphabet?

Answer 1

Are you going to just the first character?

 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 a string for you.

edit: Maybe substr (TestNames, 1,1) in sqllite, I do not have a ready-made instance to check the syntax there.

Added:

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

It does not seem optimal, but it will functionally work. Not sure which char commands should execute a series of letters in SQLlite.

I used the "top" to do this, so you don’t need to make lowercase letters in the instructions in the form ... curious hope SQLlite knows what it is.

Answer 2

try

 SELECT * FROM TestTable WHERE TestNames NOT LIKE '[^a-zA-Z]%' 

Answer 3

SELECT * FROM NC_CRIT_ATTACH WHERE substring (FILENAME, 1,1) NOT LIKE '[Az]%'; SHOULD be a little faster, since this is A) First you need to get all the data only from the first column, and then scan it. B) Still scanning the full table if you did not specify this column.