Post reply

Regular Expression

  • May 23, 2007 at 12:01 pm

    #153603

    I'm trying to write a regular expression to filter out bad account numbers in our database. We have a rule that the account number must be 18 digits long and I have tried a query similar to the following:

    select * from accounts where account like ('[0-9]{18}')

    But that is not returning anything. Then when I try:

    select * from accounts where account like ('[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')

    I get all the valid rows. Can anyone tell me what I'm doing wrong in the first statement or what I can do to correct it so I don't have to put all the [0-9]'s in there like the second statement?

    Thanks...

  • May 23, 2007 at 12:15 pm

    #708404

    Not sure how to fix the like expression but you can do it like this:

    /*** CODE ***/

    create table temp (accn varchar(25))

    insert into temp (accn) values ('123123123123123123')

    insert into temp (accn) values ('123123123123123123a')

    insert into temp (accn) values ('123123123123123123b')

    insert into temp (accn) values ('333333333333333333')

    insert into temp (accn) values ('444444444444444444')

    insert into temp (accn) values ('3333333x33333333333')

    select *

    from temp

    where len(accn)=18

    and accn not like ('%[a-z]%')

    /*** CODE ***/

    You'll need to add any other characters to be excluded from the 'not like' but other then that it should work and is at least a little more elegant.

    Cheers,

    Ben

    Cheers,

    Ben Sullins
    bensullins.com
    Beer is my primary key...

  • May 23, 2007 at 12:17 pm

    #708408

    Try this

    select

    acct from #table where acct like '[0-9]%' and len(acct) = 18

    I read the help for the LIKE operator and it does seems not to support the {}.

  • May 23, 2007 at 12:17 pm

    #708407

    I think I just answered myself....In BOL for "LIKE" it does not mention anything about the {#} in pattern matching therefore I'm assuming you can't do it as I was trying to do it above unless you use one of the SQL DBA toolkit functions here.

    http://www.sqlservercentral.com/columnists/mcoles/sql2000dbatoolkitpart2.asp

    Sorry for the unnecessary post...=)

  • May 23, 2007 at 12:17 pm

    #708406

    Thanks Ben and Camilo.

  • May 30, 2007 at 11:32 am

    #709772

    LIKE only handles a small set of wildcards, it is definately not a full-blown regular expression operator.

    The most robust way to do it is the solution you already found, "select * from accounts where account like ('[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')".  There is no version of "NOT LIKE '%[a-z]%'" that can handle tabs, carriage returns, or other goofy non-printing characters.

    Something similar to the regex {#} construction would be  "LIKE REPLICATE('[0-9]', 18)".

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply