Validating Phone Numbers

• ninel

  Ten Centuries

  Points: 1087

  More actions

  August 16, 2006 at 8:43 am

  #114605

  I have a phone field I need to validate. I need to remove periods, commas, dashes from the phone numbers.

  Does anyone know how to write this query?

  Thanks,

  Ninel

   

   

• Chuck Rivel

  SSCrazy

  Points: 2991

  More actions

  August 16, 2006 at 8:51 am

  #655279

  A simple approach would be to just use the REPLACE function nested like below.

  select replace(replace(replace(@phone, '.', ''), '-', ''), ',', '')

  store this in a function on the server and reuse in your query...

  Chuck

• Jeff Moden

  SSC Guru

  Points: 1004339

  More actions

  August 16, 2006 at 9:53 pm

  #655368

  No need for a function if you want to permanently correct the data...

   UPDATE yourtable

      SET phonenumbercol = REPLACE(

                           REPLACE(

                           REPLACE(

                           REPLACE(

                           REPLACE(

                           phonenumbercol

                           ,',','')

                           ,'-','')

                           ,'.','')

                           ,'(','')

                           ,')','')

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