I needed to format a phone number in SQL (stored in out database as a varchar(10), just numbers, no formatting) as an honest to goodness, nice looking phone number, like (517) 555-1212. And I found an appropriately named site called “Programming Tutorials” that explained exactly how to do it. I’ve added a wee bit of extra code for demonstration purposes:
DECLARE @Phone AS VARCHAR(15)
SET @Phone = ‘5175551212’
SET @Phone = CASE
WHEN LEN(ltrim(rtrim(@Phone)))=’10’ THEN ‘(‘+SUBSTRING(@Phone,1,3)+’)’+’ ‘+SUBSTRING(@Phone,4,3)+’-‘+SUBSTRING(@Phone,7,4)
ELSE ‘ ‘ end
Remember to make the variable that will hold the formatted phone number large enough to accomodate the newly inserted characters. When I first wrote the code, my @Phone variable was a varchar(10), enough to contain the original value, but not large enough for the formatted final phone number.