Formatting a phone number in T-SQL

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

SELECT @Phone

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.

Advertisements
  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: