Formatting should be done using your front end application. However if that option does not exist due to some reason, use the following T-SQL code to format Phone Numbers in SQL Server.
Note: The code shown below formats US Phone Numbers. I have covered three scenarios, however feel free to modify the code to add additional scenarios.
The 3 possible scenarios of a US Phone number input could be:
18052224353 will be returned as 1 (805) 222-4353
8052224353 will be returned as (805) 222-4353
2224353 will be returned as 222-4353
WHEN 11 THEN LEFT(phno,1)+
STUFF(STUFF(STUFF(phno,1,1,' ('),6,0,') '),11,0,'-')
WHEN 10 THEN
STUFF(STUFF(STUFF(phno,1,0,' ('),6,0,') '),11,0,'-')
WHEN 7 THEN
ELSE 'Invalid phno'
END as formatted_phno
'18052224353' as phno union all
'8052224353' as phno union all
'888052224353' as phno union all
'2224353' as phno
The SQL Server STUFF function is used to replace certain characters with other characters, based on the position. For example STUFF(phno,1,1,'(') replaces the first character with '('. Similarly STUFF(phno,6,1,') ') inserts ') ' after position 6.
Did you like this post?
|subscribe via rss||subscribe via e-mail|
|print this post||follow me on twitter|