Having trouble with wildcard/len syntax validation query

  • hi.  So I have a field where the proper format should be

    XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX

    So the pattern is 8 hyphen 4 hyphen 4 hyphen 4 hyphen and lastly 12.  It can only be 0-9 or A-Z, no other characters.

    I was using something like this "like '%[A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9]'  ...etc. but no luck

    I want to be able to find any value that does not conform to both rules in the syntax (# of characters and type)

    Example:

    E02D0914-BF24-43BC-ABC3-0D056E76C6BE

    For example, if the above had 7 characters before the first hyphen and not 8, it would be wrong.  If it had 8 characters but lets say one of them was $, it would be wrong.  Much like a phone number.  The first 3 HAS to be an area code and that can only be 3 numbers and HAS to be only numeric 0-9

    Thanks for any help

  • The LIKE approach works for me.  Do you get bad results from this (note, make sure no new-line characters invade the LIKE string):

    drop table #temp

    create table #temp
    (col1 varchar(40))

    insert into #temp values
    ('12345678-9abc-defg-hijk-lmnopqrstuvw'),-- just right
    ('12345678-9abc-defg-hijk-lmnopqrstuv'),-- group 5 too short
    ('12345678-9abc-defg-hij-lmnopqrstuvw'),-- group 4 too short
    ('12345678-9abc-defgg-hijk-lmnopqrstuvw'),-- group 3 too long
    ('12345678-9abc-defg-hijk-lmnopqrstuv$'),-- $ disallowed
    ('12345678-9abc-defg-hijk-lmnopqrstuvwxyz') -- group 5 too long

    select *
    from #temp
    where col1 like '[A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9]-[A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9]-[A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9]-[A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9]-[A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9]'
  • An stop using [A-Z] for this if these are GUIDs.  It should be "[A-D] if your server is case insensitive and {A-Da-d] if your server IS case sensitive.

    A better question would be, why on this good Green Earth are you storing GUIDs as a 36 byte character rendition instead of as a proper 16 Byte UNIQUEIDENTIFIER, which has all such checks built in.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 3 posts - 1 through 2 (of 2 total)

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