Formatting Phone Number

Question

Post reply

Formatting Phone Number

pkaraffa

SSC Enthusiast

Points: 115
More actions
December 16, 2012 at 6:32 am

#286224

I have phone number in the database that are formatted as (xxx) xxx-xxxx. What I need to do is to format the numbers as xxx-xxx-xxxx. I tried below
select replace(replace(s.home_phone,'('),')')
from students s
which obviously does not give me the right result. Can someone please help. Thanks again!

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

Is this what you require:

DECLARE @homephone VARCHAR(20)

SET @homephone='(xxx) xxx-xxxx'

SET @homephone = (select replace(@homephone,'(',''))

set @homephone = replace(@homephone,')','-')

set @homephone = replace(@homephone,' ','')

select @homephone

Result: xxx-xxx-xxxx

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

pkaraffa SSC Enthusiast Points: 115 More actions · Answer 2

pkaraffa

SSC Enthusiast

Points: 115

December 16, 2012 at 9:46 am

#1568952

Thank you so much that worked!!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Edit: nm

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1003948 More actions · Answer 4

Just as an important sidebar...

You can see the bit of trouble that you're in because someone stored a formatted phone number and you want a different format. What you can't see is that there are area code splits that occur now and then and that there are several other uses of phone numbers that you might not even be aware of, yet.

Since it looks like you're working with NANPA (North American Numbering Plan Administration), I'd like to suggest that each 10 digit phone number be broken down into its 3 component parts.

Pos Description

1-3 NPA or Area Code (NPA used to identify the values a character position could have)

4-6 NXX or Exchange (NXX used to identify the values a character position could have)

7-10 Basicly, the line number.

Among a dozen other uses, splitting the phone number up according to its parts also makes formatting (which really should be done in the presentation layer instead of the data layer) a breeze no matter which format you decide to go with. It also makes validating phone numbers a whole lot easier because most phone number validation systems use NPA/NXX to do so. If an Area Code split ever occurs, its always done by NPA/NXX. Saves on a whole lot of unnecessary coding and splitting later on. If it's absolutely necessary to do the formatting in the data layer, you should still store the phone number in its 3 parts and make a persisted calculated column to format the number for you.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ron McCullough SSC Guru Points: 63877 More actions · Answer 5

If you would like to follow Jeff Moden's suggestion (advice) then this might be what you need

DECLARE @homephone VARCHAR(20)

SET @homephone='(999)888-7777'

SET @homephone = REPLACE(replace(@homephone,'(',''),')','-')

select @homephone AS 'answer'

select SUBSTRING(@homephone,1,3) as 'NPA'

,SUBSTRING(@homephone,5,3) AS 'Exchange'

,SUBSTRING(@homephone,9,4) AS 'line number'

Results:

answer

999-888-7777

NPAExchangeline number

999888 7777

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Dwain Camps SSC Guru Points: 86908 More actions · Answer 6

Here's another example using PatternSplitCM (which can be found in the 4th link in my signature).

DECLARE @Phones TABLE

(PhoneID INT IDENTITY, PhoneNo VARCHAR(20))

INSERT INTO @Phones

SELECT '(305) 235-7513'

UNION ALL SELECT '(305) 889-5443'

SELECT PhoneID

,PhoneNumber=STUFF((

SELECT '-' + Item

FROM @Phones b

CROSS APPLY PatternSplitCM(PhoneNo, '[0-9]')

WHERE [Matched] = 1 AND a.PhoneID = b.PhoneID

ORDER BY ItemNumber

FOR XML PATH('')), 1, 1, '')

FROM @Phones a

It most likely won't be as fast as the options using REPLACE (especially if a binary collation is used), however it does offer additional flexibility in reformatting the phone number to whatever display format you'd like to see.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Jeff Moden SSC Guru Points: 1003948 More actions · Answer 7

Just for information purposes... what I said about Area Codes occurring more frequently than you'd think might be scoffed at a bit by some. Please see the following URL for what is currently planned and active (in the middle of a change or in the middle of planning but has no date schedued yet).

http://www.nationalnanpa.com/nas/public/plannedNpasNotInServiceReport.do?method=displayPlannedNpasNotInServiceReport