How to format data with customer service ph nos please

Question

Post reply

How to format data with customer service ph nos please

asita

SSCarpal Tunnel

Points: 4144
More actions
May 19, 2016 at 3:59 pm

#316719

Hello Good Morning,
Can you please help me with this phone numbers formatting,
create table #myphone (phno varchar(20))
Insert into #myphone values ('(866) 611-3847')
Insert into #myphone values ('(800) 2-KMIDAR')
Insert into #myphone values ('(800)-001-4015')
Insert into #myphone values ('(800)001-4015')
Insert into #myphone values ('(866) 231-2244')
Insert into #myphone values ('(888) 401-KOND1')
Insert into #myphone values (' (855) EZP-8107')
Insert into #myphone values ('1 (855) OKR-8107')
Insert into #myphone values ('1-800-228-2244')
Insert into #myphone values ('1-800-483-SAVE(7283)')
Insert into #myphone values ('800 228 2244')
Insert into #myphone values ('666 - 606 - 0399')
Need to replace all - to .
need to remove any spaces in between nos
need to remove brackets () if appear in first 5 characters then need to replace ) with .
Expected results as below
866.611.3847
800.2.KMIDAR
800.001.4015
800.001.4015
866.231.2244
888.401.KOND1
855.EZP.8107
1.855.OKR.8107
1.800.228.2244
1.800.483.SAVE(7283)
800.228.2244
666.606.0399
Thank you very much in advance

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

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

pietlinden SSC Guru Points: 63503 More actions · Answer 1

pietlinden

SSC Guru

Points: 63503

May 19, 2016 at 4:22 pm

#1880397

REPLACE is your friend. =)

asita SSCarpal Tunnel Points: 4144 More actions · Answer 2

Thank you for your input

I tried that but some how I am able to achievement mid level

it is ok if we can do it in steps wise (like we do in procedures) need not to be in a single statement

Can you please help me

Thanks Again

Milan

pietlinden SSC Guru Points: 63503 More actions · Answer 3

so write it to a temp table and do a series of updates on it.

asita SSCarpal Tunnel Points: 4144 More actions · Answer 4

Thank you friend

I tried below (it may helpful to otherS)

SELECT *,

CASE WHEN LEFT(REPLACE(REPLACE(REPLACE(REPLACE(LEFT(phno,7),')',''),'(',''),' ',''),'-','')+REPLACE(REPLACE(RIGHT(phno,LEN(phno)-7),' ',''),'-',''),1) = '1' THEN STUFF(STUFF(STUFF(REPLACE(REPLACE(REPLACE(REPLACE(LEFT(phno,7),')',''),'(',''),' ',''),'-','')+REPLACE(REPLACE(RIGHT(phno,LEN(phno)-7),' ',''),'-',''),5,0,'.'),9,0,'.'),2,0,'.')

ELSE STUFF(STUFF(REPLACE(REPLACE(REPLACE(REPLACE(LEFT(phno,7),')',''),'(',''),' ',''),'-','')+REPLACE(REPLACE(RIGHT(phno,LEN(phno)-7),' ',''),'-',''),4,0,'.'),8,0,'.')

END

FROM #myphone

but still have question on what If we have a phone no that is 18001231234 then it need to be 1.800.123.1234

if we have phone no that is 8002221212 then it needs to be 800.222.1212

I am trying it now

Thank you

JustMarie SSCertifiable Points: 7771 More actions · Answer 5

Remember that a CASE statement will terminate on the first true condition. So if you're trying to do multiple actions on the same value you're not going to do it in a case statement.

pietlinden has the right direction - imo - with creating a series of statements to run over your data. That way you can restrict what doesn't apply and work step by step through the process. You can also add new requirements as they come through.

Multiple steps is not inefficient when they're a better course of action.

pietlinden SSC Guru Points: 63503 More actions · Answer 6

Marie,

That's what I was thinking... It's just easier to do this in steps. Then maybe put them all in a stored procedure, if this has to be done more than once...

Sure it's not a single statement, but if you only run it once a day, so what? Not like it's running every 20 seconds all day long.

Lowell SSC Guru Points: 323518 More actions · Answer 7

here's how i started tackling it: strip everything out, and then format depending on 10 or 11 digit lengths.

I think i'd remove letters and replace with their corresponding numbers...

I'm ignoring inserting periods before and after words in the phone;

/*

HowsThis

866.611.3847

800.2KM.IDAR

800.001.4015

866.231.2244

888.401.KOND1

855.EZP.8107

1.855.OKR.8107

1.800.228.2244

1.800.483.SAVE(7283)

800.228.2244

666.606.0399

*/

IF OBJECT_ID('tempdb.[dbo].[#myphone]') IS NOT NULL

DROP TABLE [dbo].[#myphone]

create table #myphone (phno varchar(20))

Insert into #myphone values ('(866) 611-3847')

Insert into #myphone values ('(800) 2-KMIDAR')

Insert into #myphone values ('(800)-001-4015')

Insert into #myphone values ('(800)001-4015')

Insert into #myphone values ('(866) 231-2244')

Insert into #myphone values ('(888) 401-KOND1')

Insert into #myphone values (' (855) EZP-8107')

Insert into #myphone values ('1 (855) OKR-8107')

Insert into #myphone values ('1-800-228-2244')

Insert into #myphone values ('1-800-483-SAVE(7283)')

Insert into #myphone values ('800 228 2244')

Insert into #myphone values ('666 - 606 - 0399')

SELECT * FROM #myphone

CROSS APPLY(SELECT REPLACE(REPLACE(REPLACE(

phno,'.',''), --dots

'-',''), --dashes

' ','') --whitespace

As FirstPass) AS SimpleClean

CROSS APPLY(SELECT LEFT(FirstPass,6) As Section,

RIGHT(FirstPass,LEN(FirstPass) -6) As RearSection,

REPLACE(REPLACE(LEFT(FirstPass,6),'(',''),')','') + RIGHT(FirstPass,LEN(FirstPass) -6 ) As ReplacedSection) AS Parens

CROSS APPLY(SELECT CASE

WHEN LEFT(ReplacedSection,1)='1'

THEN STUFF(STUFF(STUFF(ReplacedSection,2,0,'.'),6,0,'.'),10,0,'.')

ELSE STUFF(STUFF(ReplacedSection,4,0,'.'),8,0,'.')

END AS HowsThis) AS LowellsVersion

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!