Advertisement
Help Keep Boards Alive. Support us by going ad free today. See here: https://subscriptions.boards.ie/.
If we do not hit our goal we will be forced to close the site.

Current status: https://keepboardsalive.com/

Annual subs are best for most impact. If you are still undecided on going Ad Free - you can also donate using the Paypal Donate option. All contribution helps. Thank you.
https://www.boards.ie/group/1878-subscribers-forum

Private Group for paid up members of Boards.ie. Join the club.
Hi all, please see this major site announcement: https://www.boards.ie/discussion/2058427594/boards-ie-2026

Simple SQL - representing a number

  • 19-03-2004 03:29PM
    #1
    Dr Pepper
    Registered Users, Registered Users 2 Posts: 821 ✭✭✭


    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


Comments

  • #2
    Enygma
    Closed Accounts Posts: 1,651 ✭✭✭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


  • #3
    jd
    Registered Users, Registered Users 2, Paid Member Posts: 5,822 ✭✭✭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


  • #4
    bonkey
    Registered Users, Registered Users 2 Posts: 15,443 ✭✭✭✭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


Advertisement