6

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?

XSL
  • 2,965
  • 7
  • 38
  • 61
  • Just non-alpha, so it should return anything beginning with `1,2,_, etc..`) – XSL Jun 25 '12 at 20:17
  • That `[]` range syntax is SQL Server. [Can't see any indication that SQLLite supports it in the docs](http://www.sqlite.org/lang_expr.html) – Martin Smith Jun 25 '12 at 20:46
  • Thanks Martin, that might be the issue. My SQL skills aren't all that great, but is there an alternative method of performing something similar? – XSL Jun 25 '12 at 20:50

3 Answers3

8

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.

Twelfth
  • 7,070
  • 3
  • 26
  • 34
  • Thanks, but it's still returning every row (same as if I did `select * from TestTable`) – XSL Jun 25 '12 at 20:17
  • Does select substr(TestNames,1) from testable return just the first char for you? If it does, we just need to alter the not like statement to something more SQLLite friendly. – Twelfth Jun 25 '12 at 21:33
  • 1
    `substr(TestNames,1,1)` works fine so it seems that the `not like` statement is the issue. – XSL Jun 25 '12 at 22:16
  • 1
    Wanna do this the silly way? select * from TestTable WHERE upper(substr(TestNames,1,1)) NOT IN ('A','B','C'...) Yes, I am suggesting you do a not in and put each letter in individually. Upper means it's always upper case and you won't need to worry about lower case. I'm not sure if there is any other method in SQLlite to do a char range. Put into the answer for you. – Twelfth Jun 25 '12 at 22:35
  • I'll use this suggestion for now and mark it as answer tomorrow if nothing else comes up in between. Thanks for your help! – XSL Jun 25 '12 at 22:50
  • I know, definitely not the prettiest solution I've come with up on stack overflow. – Twelfth Jun 25 '12 at 22:55
  • I'll go with this solution. Not any real problems performance wise (only 26 characters) so should suffice. Thanks again. – XSL Jun 26 '12 at 10:40
  • 1
    heres the full string to save time for others. select * from TestTable WHERE Upper(substr(TestNames,1,1)) NOT in ('A','B','C','D','E','F','G','H', 'I', 'J','K','L','M','N', 'O', 'P', 'Q', 'R','S', 'T', 'U', 'V', 'W', 'X','Y', 'Z' – Dan Hastings Jan 10 '15 at 14:23
1

try 

SELECT * FROM TestTable WHERE TestNames NOT LIKE '[^a-zA-Z]%'
Eswar Rajesh Pinapala
  • 4,841
  • 4
  • 32
  • 40
  • That's also returning everything. Is this because of Sqlite's lack of built in regex? – XSL Jun 25 '12 at 20:38
  • sqlite3 supports regex [ WHERE fld REGEXP ] should work.. whats urs? – Eswar Rajesh Pinapala Jun 25 '12 at 20:49
  • sqlite3, but when I use that, I get `no such function: REGEXP`. That function doesn't work out the box and I've had troubles trying to get it working. Am hoping to avoid regex for this type of call http://stackoverflow.com/questions/5071601/how-do-i-use-regex-in-a-sqlite-query – XSL Jun 25 '12 at 20:54
  • i belive regexp is not supported out of the box! http://www.sqlite.org/lang_expr.html – Eswar Rajesh Pinapala Jun 25 '12 at 20:57
  • There may be a typo here. We are looking for all nonalpha characters, but this will return all alpha characters since the two nots here cancel each other out –  May 24 '16 at 04:48
0

SELECT * FROM NC_CRIT_ATTACH WHERE substring(FILENAME,1,1) NOT LIKE '[A-z]%'; SHOULD be a little faster as it is A) First getting all of the data from the first column only, then scanning it. B) Still a full-table scan unless you index this column.

PCPGMR
  • 340
  • 2
  • 7