CODEWITHSUNDEEP
sql-server
Manjuboyz
Manjuboyz

Reputation: 7066

Formatting multiple phone numbers with Parenthesis[ () ] and hyphen( - ) in SQL Server

I have a result set in SQL and which contains set of phone numbers with comma separated, earlier we had only one phone number and I could able to format the phone numbers I need i.e.

(123)456-7890

Now this formula doesn't work since I have more than 10 digits in my SQL column, so is there any way I can achieve this transaction in SQL? Here is my SQL output. enter image description here

Btw I am getting this comma separated value from this code.

SELECT
EmployeeID,
Type,
STUFF(
    (
    SELECT
        ', ' + PhoneNumber
    FROM EmpPhone E
    WHERE E.EmployeeID = T.EmployeeID AND E.Type = T.Type
    FOR XML PATH(''),TYPE
    ).value('.','VARCHAR(MAX)'
    ), 1, 1, ''
) AS Phones
INTO #PhoneNumbers
    FROM EmpPhone AS T
    GROUP BY EmployeeID, Type

so my question is can the query result be made like 

(919)382-9999,(888)888-8888,(222)-2222 ......

Upvotes: 2

Views: 3918

Answers (1)

DineshDB
DineshDB

Reputation: 6193

Try this,

SELECT
EmployeeID,
Type,
STUFF(
    (
    SELECT
        ', ' + '('+LEFT(PhoneNumber,3)+')'+RIGHT(LEFT(PhoneNumber,6),3)+'-'+RIGHT(PhoneNumber,4)
    FROM EmpPhone E
    WHERE E.EmployeeID = T.EmployeeID AND E.Type = T.Type
    FOR XML PATH(''),TYPE
    ).value('.','VARCHAR(MAX)'
    ), 1, 1, ''
) AS Phones
INTO #PhoneNumbers
    FROM EmpPhone AS T
    GROUP BY EmployeeID, Type

Hope, this helps you.

Upvotes: 2

Related Questions