Update Telephone Numbers Format Change 1

[alexanderthegreat]

Hi I want to update my feild telephone1 to look like this
(XXX)-XXX-XXXX but the problem is some of the fields have two numbers like this

2126296602 2125631131

but most have just the one
2126296602

How could accomplish this?

Al

 

[jegaby]

I assume that your problem is in dealing with the fields with two numbers, not in the formatting itself... You could use something like this..

SELECT CASE
WHEN CHARINDEX(' ',LTRIM(RTRIM(telephone1))) = 0
THEN --put conversion for a single number here
ELSE
--put conversion for two numbers here
END AS FormattedTelephone
FROM TableName