SQL query help!! I'm trying to select the row that DOESN'T start with a number

I have 10,001 rows in my table, and all of the rows except one start with a number. I need to find this one row that doesn开发者_如何学编程't start with a number, or even that doesn't contain a number.

So this is what I have:

Select col1 from table1 where col1 not like '?%'

Is this even close? I need to find the row that doesn't have a number...

Thanks!!

UPDATE: I am using a sqlite database

Use:

SELECT col1
  FROM table1 
 WHERE SUBSTR(col1, 1, 1) NOT BETWEEN 0 AND 9

Reference:

• core functions (incl SUBSTR)
• LIKE

On Sql Server,

Select * From table
Where col1 Like '[^0-9]%'

EDIT: Don't know if there is an equivilent on SQLLIte,

but this will work...

Select * From table
Where col1 Not Like '0%' 
   And col1 Not Like '1%'
     ...
   And col1 Not Like '9%'

There is a post in code project that allow you to use Regex with Ms SQL.

Hope this help.

The easiest way might be to just note that you're not using numbers; you're using strings that happen to have numbers in them. In this case, you can do

select * from table1 where col1 not between '0' and '9:'; 

(where the colon is the ASCII character after '9'; this way '9999999' won't be found).

It should be a lot less expensive than some of the other suggestions (e.g., checking the value of the first character).

@Dueber got me thinking, couldn't you just do this?

SELECT * FROM table1 WHERE col1 > '9'

Grab the first character, see if it's numeric.

SELECT *
FROM   table1
WHERE  ISNUMERIC(SUBSTRING(col1,1,1)) = 0

SUBSTRING

ISNUMERIC