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