CODEWITHSUNDEEP
sqlsqlitesql-like
Jerry
Jerry

Reputation: 537

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

Upvotes: 5

Views: 4833

Answers (6)

Mehmet Şener
Mehmet Şener

Reputation: 1

Use:

IsNumeric(Substring(col1,1,1))!=1

Upvotes: 0

OMG Ponies
OMG Ponies

Reputation: 332591

Use:

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

Reference:

Upvotes: 6

Brad
Brad

Reputation: 15577

@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

Upvotes: 0

Charles Bretana
Charles Bretana

Reputation: 146499

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%'

Upvotes: 4

Bill Dueber
Bill Dueber

Reputation: 2706

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).

Upvotes: 0

Daniel Mahadi
Daniel Mahadi

Reputation: 918

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

Hope this help.

Upvotes: 0

Related Questions