CODEWITHSUNDEEP
sqlsql-server-2008-r2
Jordan
Jordan

Reputation: 399

Format Phone Number inside of concat field SQL

I have a unique circumstance here. I have a concatenated field I call address and as part of this address I have the phone number of that address on the last line. currently the phone number is formatted like:

8885551212

I want it to format like:

(888) 555-1212

Below is my INSERT into my temp table that I do for getting everything correct. Any guidance on how to get the phone portion working right is a great help. I did find one article talking about using a function but I am unsure how to write it up properly for my circumstance. Thank you.

INSERT into #Referral_Temp (status, physician, timeFrame, date, address)
    select o.actStatus, o.actTextDisplay, apptTimeframe, apptDate,
    (select (p.address_line_1 + CHAR(13)+CHAR(10) + p.address_line_2 + CHAR(13)+CHAR(10)
             + p.city + ', ' + p.state + ' ' + p.zip + CHAR(13)+CHAR(10) + p.phone) 
             AS address)
    from order_ o
    join provider_mstr p on o.refer_to_prov_id = p.provider_id
    where o.encounterID = @enc_id

Upvotes: 0

Views: 3020

Answers (3)

HaveNoDisplayName
HaveNoDisplayName

Reputation: 8497

Try this

SELECT '(' + SUBSTRING('8885551212',1,3) + ') ' + SUBSTRING('8885551212',4,3) + '-' + SUBSTRING('8885551212',7,4)

Output will be :- (888) 555-1212

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93744

Use Stuff string function to insert the required special into the string. Try this.

SELECT '('+ Stuff(Stuff('8885551212', 4, 0, ') '), 9, 0, '-')

Upvotes: 1

D Stanley
D Stanley

Reputation: 152626

You can use the SUBSTRING function:

change 

+ p.phone

to

+ '(' + SUBSTRING(p.phone,1,3) + ') ' 
+ SUBSTRING(p.phone,4,3) + '-' 
+ SUBSTRING(p.phone,7,4)

Upvotes: 1

Related Questions