To format this data so that all phone numbers are stored in the following format:
(999) 999-9999
for US phone numbers.
phone
----------
6055552862
5615552700
9045555680
5805555371
2815558368
2545558430
3365552797
3365557233
that I want to convert to:
phone
----------
(605) 555-2862
(561) 555-2700
(904) 555-5680
(580) 555-5371
(281) 555-8368
(254) 555-8430
(336) 555-2797
(336) 555-7233
I decided to use STUFF. This is a string function that allows you to delete characters from a string and insert a string value into another string with control over the positioning of the insertion. The difference from REPLACE is that this function uses a position in the string to make replacement rather than a pattern.
The format for STUFF is as follows:
STUFF ( character_expression 1, start , length , character_expression 2)
where the parameters are:
Character_expression 1 - The string expression in which to insert data
Start - The start position for the insertion
Length - The number of characters in character_expression 1 to delete.
Character_expression 2 - the string to insert into character_expression 1
declare @d char( 14)
select @d = '6055552862'
select stuff( stuff( stuff( stuff( @d,1 ,0, '('), 5, 0, ')'), 6, 0, ' '), 10, 0, '-')
which yeilds
(605) 555-2862
