Post reply

Query for specific format

  • January 1, 2014 at 6:48 pm

    #286684

    I'm trying to find discrepancies in a telephone column in the client table and identify any telephone number that is not in the following format

    (555) 555-5555

    I've been digging around a bit and am a bit stuck as to what I should put in my where clause.

    Thanks in advance

  • January 2, 2014 at 1:13 am

    #1676801

    SELECT columnlist

    FROM dbo.SomeTable

    WHERE PhoneNumber NOT LIKE '([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'

    Edit: Moved out of place parenthesis.

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

  • January 2, 2014 at 5:47 am

    #1676853

    Perfect!

  • January 2, 2014 at 7:15 am

    #1676895

    caippers (1/2/2014)

    Perfect!

    Glad to be of help. The next question is, do you understand how it works?

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

  • January 2, 2014 at 8:26 am

    #1676927

    I do the only question I have is I don't see a consideration for the left and right parentheses. Wouldn't this miss that if a number was missing the right parentheses for example. (555 555-5555

  • January 2, 2014 at 8:31 am

    #1676934

    caippers (1/2/2014)

    I do the only question I have is I don't see a consideration for the left and right parentheses. Wouldn't this miss that if a number was missing the right parentheses for example. (555 555-5555

    It's in the end of the string. I assume it was a mistake from Jeff while building the expression (possibly due to Intellisense), just move it to the correct position.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • January 2, 2014 at 10:53 am

    #1677005

    Luis Cazares (1/2/2014)

    caippers (1/2/2014)

    I do the only question I have is I don't see a consideration for the left and right parentheses. Wouldn't this miss that if a number was missing the right parentheses for example. (555 555-5555

    It's in the end of the string. I assume it was a mistake from Jeff while building the expression (possibly due to Intellisense), just move it to the correct position.

    Correct. Thanks, Luis. I went back and corrected that post. It was 3AM and I had run out of coffee about 4 hours before that. :blush:

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

  • January 2, 2014 at 8:12 pm

    #1677100

    Thanks again. I do understand how it works. I was trying to do a REGEXP_SUBSTR but it seemed that I had to do a UDF to do the equivilant in T-SQL.

    Either way this works!

    I come for oracle world so I'm slowly coming along.

    Thanks again!!

  • January 3, 2014 at 8:28 am

    #1677307

    Excellent. Thanks for the feedback.

    On the Oracle shift that you're having to make (I had to work with Oracle for about 3 years), my best advice would be to follow the idea of the "paradigm shift" that I have in my signature line below. When I first started out in T-SQL, that was probably the best lesson that I ever learned.

    --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 9 posts - 1 through 8 (of 8 total)

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