April 16, 2009 at 3:13 pm
How To Display Phone Number Formatted
How To Apply Mask To Phone Number
Q: How best to display a phone number that is stored raw (without any formatting or delimeters) and display it in a common format such as ‘(xxx) xxx-xxxx’.
I know this is one that has been around the internet block several times and while I do have a couple of ways to do this now I’d like to see if there is an even better way to do this then either of the 2 methods we currently use.
METHOD 1: Using Substring() – I’ve found this listed numerous times in forums and articles all over the internet
SELECT T.sPhoneNum1 AS 'Phone Number - Raw/Unformatted',
'(' + Substring(T.sPhoneNum1,1,3) + ') '
+ Substring(T.sPhoneNum1,4,3) + '-'
+ Substring(T.sPhoneNum1,7,4) AS 'Phone Number-Formatted'
FROM (SELECT '9991112222' AS 'sPhoneNum1') T
METHOD 2: Using Stuff() – I personally like this one because it involves 1 less string function and several less string concatenations then the first method
SELECT T.sPhoneNum1 AS 'Phone Number - Raw/Unformatted',
'(' + Stuff(Stuff(T.sPhoneNum1,7,0,'-'),4,0,') ') AS 'Phone Number-Formatted'
FROM (SELECT '9991112222' AS 'sPhoneNum1') T
Now the above is designed for US based numbers that consist of at most an area code and the 7 digit local number and so it might not work for numbers outside of the US. Does anyone know of a more effecient way to take a phone number stored raw and retrun it formatted like this?
What about if the phone number may (or may not) include an extension?
Just looking for different takes on how best to do this.
Kindest Regards,
Just say No to Facebook!April 16, 2009 at 3:13 pm
I'm also curious if anyone knows if MS is working on any future Mask() function for T-SQL that would do this kind of thing but on a much more flexible scale?
What would be great is to have a Native function that could be used for Input and/or Output so that you could call it to display a raw phone number formatted as well as pass to it a formatted value and a mask or pattern and have it return that value with the mask/pattern stripped out.
Kindest Regards,
Just say No to Facebook!April 16, 2009 at 4:17 pm
You didn't learn to program in RPG did you? 😀
I've used both SUBSTRING and STUFF. Lately I've stayed with STUFF. You should also test the string length test, and the first character, to see if an area code was included or a 1 prefix to dial long distance. (In the U.S.) When I get an "odd" length (like 5 digits), I just leave the number unformatted.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 17, 2009 at 12:44 am
Hi,
Try this,
DECLARE @Phone NVARCHAR(50)
SET @Phone = '(xxx) xxx-xxxx'
DECLARE
@LoopINT
,@In_LenINT
,@FormatedPhoneNVARCHAR(50)
,@Check_CharCHAR(1)
SET @Loop = 1
SET @FormatedPhone = @Phone
WHILE @Loop <= 11
BEGIN -- Outer Loop
SET @Check_Char =
CASE @Loop
WHEN 1 THEN '"'
WHEN 2 THEN '.'
WHEN 3 THEN '-'
WHEN 4 THEN '_'
WHEN 5 THEN ' '
WHEN 6 THEN '('
WHEN 7 THEN ')'
WHEN 8 THEN ','
WHEN 9 THEN '+'
WHEN 10 THEN '['
WHEN 11 THEN ']'
ELSE ' '
END
SET @FormatedPhone = REPLACE( @FormatedPhone, @Check_Char, '' )
SET @Loop = @Loop + 1
END
SELECT @FormatedPhone
April 17, 2009 at 7:28 am
I'd let the UI do the formatting. In the case of a system that I am currently involved in developing that includes international phone numbers you could include a format in the countries table and pass that to the UI along with the raw text and allow the UI to apply the format.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 17, 2009 at 7:43 am
Good point, Jack. Although we do formatting at the db level for consistency, if you just stored phone numbers as integers, you would trim fat out of the row size as well.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 20, 2009 at 10:16 am
Jack Corbett (4/17/2009)
I'd let the UI do the formatting. In the case of a system that I am currently involved in developing that includes international phone numbers you could include a format in the countries table and pass that to the UI along with the raw text and allow the UI to apply the format.
I too would normally let the reporting tool/app do the formatting in every possible case but for this scenario the reporting end is part of a proprietary accounting software app and we are stuck using their reporting tool. We're lucky that we can even directly edit the SQL because we have the higher end version (the Enterprise version) of the product; non-enterprise clients have a dumb down gui with seriously limited functionality. And so to that end all of my flexibility is what I can do with the context of a T-SQL query and so I'm trying to produce the phone number formatted in as few steps or executions of code as possible for performance reasons.
Our accoutring DB is over 100GB and while that may not be that big when compared to DB's of the world it puts us in the top 10 -20 users of this accounting software. And because you know most software vendors developers test against small test DB's that do not represent real world size or data, we have to be very performance conscious in everything we do.
We also can't change the table/DB design aside form adding our own custom SP's, UDF's & views so altering the table to store a phone number format along with the number is not an option.
Thanks for the input though
Kindest Regards,
Just say No to Facebook!April 20, 2009 at 10:19 am
Bob Hovious (4/16/2009)
You didn't learn to program in RPG did you? 😀I've used both SUBSTRING and STUFF. Lately I've stayed with STUFF. You should also test the string length test, and the first character, to see if an area code was included or a 1 prefix to dial long distance. (In the U.S.) When I get an "odd" length (like 5 digits), I just leave the number unformatted.
I did not include teh actual SQL we use for the sake of brevity, opting jsut to show a basic SELECT with the same combination of functions. The ocmplte code is such that we don't have to deal with whether a 1 is included or nit, only the possability that there may be an extesion which could be anywhere from 1 to 5 digits in length.
Thanks for replying.
Kindest Regards,
Just say No to Facebook!April 20, 2009 at 10:22 am
Rajesh Jonnalagadda (4/17/2009)
Hi,Try this,
DECLARE @Phone NVARCHAR(50)
SET @Phone = '(xxx) xxx-xxxx'
DECLARE
@LoopINT
,@In_LenINT
,@FormatedPhoneNVARCHAR(50)
,@Check_CharCHAR(1)
SET @Loop = 1
SET @FormatedPhone = @Phone
WHILE @Loop <= 11
BEGIN -- Outer Loop
SET @Check_Char =
CASE @Loop
WHEN 1 THEN '"'
WHEN 2 THEN '.'
WHEN 3 THEN '-'
WHEN 4 THEN '_'
WHEN 5 THEN ' '
WHEN 6 THEN '('
WHEN 7 THEN ')'
WHEN 8 THEN ','
WHEN 9 THEN '+'
WHEN 10 THEN '['
WHEN 11 THEN ']'
ELSE ' '
END
SET @FormatedPhone = REPLACE( @FormatedPhone, @Check_Char, '' )
SET @Loop = @Loop + 1
END
SELECT @FormatedPhone
Rajeesh - Thanks for taking the time to provide such a complete example but what we are going for is speed/performance more so then flexability. We don't have to deal with possible combinations of number types such as with our without area code. We only have to deal with the possability of an extension. All numbers with or without an extension are going to be 10 digits in length (3 digit area code + 7 digit local number) and so anything past the 10th character from lefgt to right is treated as the extenions.
Thanks though for taking the time to post.
Kindest Regards,
Just say No to Facebook!April 22, 2009 at 12:07 pm
Bob Hovious (4/17/2009)
Good point, Jack. Although we do formatting at the db level for consistency, if you just stored phone numbers as integers, you would trim fat out of the row size as well.
Heh... better be BIGINT's unless you split the NPA and NXX into separate columns.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 22, 2009 at 12:10 pm
YSLGuru (4/16/2009)
How To Display Phone Number FormattedHow To Apply Mask To Phone Number
Q: How best to display a phone number that is stored raw (without any formatting or delimeters) and display it in a common format such as ‘(xxx) xxx-xxxx’.
I know this is one that has been around the internet block several times and while I do have a couple of ways to do this now I’d like to see if there is an even better way to do this then either of the 2 methods we currently use.
METHOD 1: Using Substring() – I’ve found this listed numerous times in forums and articles all over the internet
SELECT T.sPhoneNum1 AS 'Phone Number - Raw/Unformatted',
'(' + Substring(T.sPhoneNum1,1,3) + ') '
+ Substring(T.sPhoneNum1,4,3) + '-'
+ Substring(T.sPhoneNum1,7,4) AS 'Phone Number-Formatted'
FROM (SELECT '9991112222' AS 'sPhoneNum1') T
METHOD 2: Using Stuff() – I personally like this one because it involves 1 less string function and several less string concatenations then the first method
SELECT T.sPhoneNum1 AS 'Phone Number - Raw/Unformatted',
'(' + Stuff(Stuff(T.sPhoneNum1,7,0,'-'),4,0,') ') AS 'Phone Number-Formatted'
FROM (SELECT '9991112222' AS 'sPhoneNum1') T
Now the above is designed for US based numbers that consist of at most an area code and the 7 digit local number and so it might not work for numbers outside of the US. Does anyone know of a more effecient way to take a phone number stored raw and retrun it formatted like this?
What about if the phone number may (or may not) include an extension?
Just looking for different takes on how best to do this.
How often will a given phone number change? I'll bet, not often. Use the STUFF formula in a persisted computed column to keep from recalculating the same thing over and over and over and....
--Jeff Moden
Change is inevitable... Change for the better is not.
April 22, 2009 at 12:20 pm
Heh... better be BIGINT's unless you split the NPA and NXX into separate columns.
I'm from Mississippi, Jeff. Our phone numbers are only 5 digits long. 😎
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 22, 2009 at 12:21 pm
Jeff Moden (4/22/2009)
YSLGuru (4/16/2009)
How To Display Phone Number FormattedHow To Apply Mask To Phone Number
Q: How best to display a phone number that is stored raw (without any formatting or delimeters) and display it in a common format such as ‘(xxx) xxx-xxxx’.
I know this is one that has been around the internet block several times and while I do have a couple of ways to do this now I’d like to see if there is an even better way to do this then either of the 2 methods we currently use.
METHOD 1: Using Substring() – I’ve found this listed numerous times in forums and articles all over the internet
SELECT T.sPhoneNum1 AS 'Phone Number - Raw/Unformatted',
'(' + Substring(T.sPhoneNum1,1,3) + ') '
+ Substring(T.sPhoneNum1,4,3) + '-'
+ Substring(T.sPhoneNum1,7,4) AS 'Phone Number-Formatted'
FROM (SELECT '9991112222' AS 'sPhoneNum1') T
METHOD 2: Using Stuff() – I personally like this one because it involves 1 less string function and several less string concatenations then the first method
SELECT T.sPhoneNum1 AS 'Phone Number - Raw/Unformatted',
'(' + Stuff(Stuff(T.sPhoneNum1,7,0,'-'),4,0,') ') AS 'Phone Number-Formatted'
FROM (SELECT '9991112222' AS 'sPhoneNum1') T
Now the above is designed for US based numbers that consist of at most an area code and the 7 digit local number and so it might not work for numbers outside of the US. Does anyone know of a more effecient way to take a phone number stored raw and retrun it formatted like this?
What about if the phone number may (or may not) include an extension?
Just looking for different takes on how best to do this.
How often will a given phone number change? I'll bet, not often. Use the STUFF formula in a persisted computed column to keep from recalculating the same thing over and over and over and....
Replies like this are exactly the reason why SQLServerCentral.com pays you the BIG bucks! This is a great answer and I would implement it if it weren't for the fact that doing so risks breakiing/bending our support with the vendors who's paccounting software uses the DB.
Thanks for sharing though because even though I can't do this in the DB this is a really good suggestion and I hope others make note of it to as I'm sure at least 1 other person out there could make use of this suggestion.
Thanks Again Jeff!
Kindest Regards,
Just say No to Facebook!April 22, 2009 at 12:22 pm
Bob Hovious (4/22/2009)
Heh... better be BIGINT's unless you split the NPA and NXX into separate columns.
I'm from Mississippi, Jeff. Our phone numbers are only 5 digits long. 😎
Bob - I know this is off-topic but where did you get the image for your Avatar/signature in the posts? That's a really cool image.
Kindest Regards,
Just say No to Facebook!April 22, 2009 at 12:32 pm
Hey Guru:
I like your Lobo-esque avatar too. I can't remember the exact site where I got this one (which is currently an angled perspective in blue on a black background). Check back in a few hours and it may change.
I use Google's image search to find biohazard emblems. There are some public sites like photobucket where people post up images. It was probably from one of those.
I like using biohazard symbols because so many have been created that I can switch them around from time to time to suit my mood but still have it be recognizably "my" logo.
Bob
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 15 posts - 1 through 15 (of 32 total)
You must be logged in to reply to this topic. Login to reply