Simple SQL - representing a number

Dr Pepper

Hi,

Quick SQL question: I want to have a query to select all IDs that start with a digit (0-9) without typing out "where id like "0%" or id like "1%"........."
I remember seeing it before but I can't remember the syntax :dunno: I'm using mySQL!

Thanks,
Brian

Enygma

Check out this page on the MySQL manual

http://www.mysql.com/doc/en/String_comparison_functions.html

Look for the parts on Regular Expressions (REGEX).

This wouldn't be the most portable way to do it though I'd imagine

jd

Originally posted by Dr Pepper
Hi,

Quick SQL question: I want to have a query to select all IDs that start with a digit (0-9) without typing out "where id like "0%" or id like "1%"........."
I remember seeing it before but I can't remember the syntax :dunno: I'm using mySQL!

Thanks,
Brian

use substr and between- would work in Oracle
John

bonkey

One option is to do it as follows...

I'm assuming an ID field of length 6....obviously you can do the same for any other size. ALso, if you can place more intelligent bounds, then its even easier

Where id >= '0' and id <='9zzzzz'

Alternately :

Where id between '0' and '9zzzzz'

If your DB implementation sorts numbers before letters, and you know that ID's will always be alphanumeric, you can go with :

where id >= '0' and id < 'a'

(Depending on case-sensitivity and/or case sorting, you may need to use capitals instead of lowercase)

Its may be faster to do it that way instead of using substr, because >= etc allow index lookups on any DB system I'm aware of, whereas substr may not always.

Oh, and in some implementations (e.g. MS-SQL) you could use :

where id like '[0-9]%'

Hope that helps.

jc