Phone Number Normalization even with literals
The script will do the following:
- build a ten digit phone number regardless of input column size.
- Ignores extensions
- Ignores leading 1, as in 1(800)...
- Ignores most special characters or spaces
- converts literals to digits
It can be easily convert to a UDF
Enjoy.
C. Z. Ovits
select replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(
replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(
replace(replace(upper(
left(
replace(replace(replace(replace(replace(replace(replace(replace(
replace(isnull(nullif(left(replace(replace(replace(replace(replace(replace(ltrim(replace(
replace(phone,' ',''),'(','')),')',''),'-',''),'.',''),'/',''),',',''),'#',''),1),'1'),' '),' ','')+
substring(replace(replace(replace(replace(replace(replace(ltrim(replace(replace(phone,' ',''),
'(','')),')',''),'-',''),'.',''),'/',''),',',''),'#',''),2,13),' ',''),
'(',''),')',''),'-',''),'.',''),'/',''),',',''),'#','')
,10)
),'A','2'),'B','2'),'C','2'),'D','3'),'E','3'),'F','3'),'G','4'),'H','4'),'I','4'),'J','5'),'K','5')
,'L','5'),'M','6'),'N','6'),'O','6'),'P','7'),'Q','7'),'R','7'),'S','7'),'T','8'),'U','8'),'V','8')
,'W','9'),'X','9'),'Y','9'),'Z','9')
from testtable 
