SQL Select–Format for phone number

 

If you want to create a SQL query to format a phone number based on different formats that may be in a database you can use this. Modify to fit your needs.

### – Replace this with your 3 digit area code. This is used when only 7 digits are stored as the value.

Home/Cell – Replace this with your phone number column

 

   1: SELECT 

   2:       CASE

   3:           WHEN LEN(REPLACE(REPLACE(REPLACE(REPLACE(Home,')',''),'(',''),' ',''),'-','')) = '7'

   4:             THEN '###' + REPLACE(REPLACE(REPLACE(REPLACE(Home,')',''),'(',''),' ',''),'-','')

   5:           WHEN SUBSTRING (Home, 1, 1) = '1'

   6:             THEN SUBSTRING(REPLACE(REPLACE(REPLACE(REPLACE(Home,')',''),'(',''),' ',''),'-',''), 2, LEN(Home))

   7:             ELSE REPLACE(REPLACE(REPLACE(REPLACE(Home,')',''),'(',''),' ',''),'-','')

   8:       END As [Home Phone],

   9:       CASE

  10:           WHEN LEN(REPLACE(REPLACE(REPLACE(REPLACE(Cell,')',''),'(',''),' ',''),'-','')) = '7'

  11:             THEN '###' + REPLACE(REPLACE(REPLACE(REPLACE(Cell,')',''),'(',''),' ',''),'-','')

  12:           WHEN SUBSTRING (Cell, 1, 1) = '1'

  13:             THEN SUBSTRING(REPLACE(REPLACE(REPLACE(REPLACE(Cell,')',''),'(',''),' ',''),'-',''), 2, LEN(Cell))

  14:             ELSE REPLACE(REPLACE(REPLACE(REPLACE(Cell,')',''),'(',''),' ',''),'-','')

  15:       END As [Cell Phone]

  16:  

  17:   FROM DBNAME

Leave a Reply

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>