Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Transact-sql for changing a telephone number Expand / Collapse
Author
Message
Posted Tuesday, March 10, 2009 2:50 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, February 29, 2012 7:33 AM
Points: 19, Visits: 60
Absolutely brilliant but they have now thrown a curved ball and state that some of the phone numbers are from other countries and so the zero needs also to stay in the brackets


Original

+37 (0) 123 5555555

Reformatted

+37 (0123) 5555555

I think a bit of tweeking needs to be done.
Post #672225
Posted Tuesday, March 10, 2009 7:21 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, December 18, 2014 9:58 AM
Points: 13,872, Visits: 9,600
Yeah, best find out all the business rules first.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #672401
Posted Tuesday, March 10, 2009 1:53 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 7:27 AM
Points: 35,769, Visits: 32,437
Spikemarks (3/10/2009)
Absolutely brilliant but they have now thrown a curved ball and state that some of the phone numbers are from other countries and so the zero needs also to stay in the brackets


Original

+37 (0) 123 5555555

Reformatted

+37 (0123) 5555555

I think a bit of tweeking needs to be done.


Heh... so tweek it! I changed 1 character to make it meet the new requirements which is also the beauty of the Divide'n'Conquer method. ;)

--===== Create and populate a test table.  This is NOT part of the solution
DECLARE @PhoneNumbers TABLE (Original VARCHAR(30))
INSERT INTO @PhoneNumbers (Original)
SELECT '+44 (0) 1908 123 456' UNION ALL
SELECT '+44 (0) 121 430 4992'

SELECT Original,
REPLACE(STUFF(PartialFormat,CHARINDEX(' ',PartialFormat),0,')'),'(',' (') AS Reformatted
FROM (--==== Partially reformat the phone number by replacing the (0) and surrounding spaces
-- with just a left parentheses...
SELECT Original, REPLACE(Original, ' (0) ','(0') AS PartialFormat FROM @PhoneNumbers
) d

Heh... send beer... I already have enough pretzels. :P


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #672753
Posted Wednesday, March 11, 2009 2:40 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, February 29, 2012 7:33 AM
Points: 19, Visits: 60
I have tweeked the code and have put it inside a cursor which should read the first 3 chars and then change to which ever it should be.

Once I have tested etc I will post for other people to use.

Beer in post!
Post #673052
Posted Wednesday, March 11, 2009 5:54 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, April 30, 2014 10:27 AM
Points: 1,001, Visits: 160
hi
i think gsquare's solution is verywell,
works fine


Aram Koukia: http://www.koukia.ca
Post #673130
Posted Wednesday, March 11, 2009 8:59 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 7:27 AM
Points: 35,769, Visits: 32,437
Spikemarks (3/11/2009)
I have tweeked the code and have put it inside a cursor which should read the first 3 chars and then change to which ever it should be.

Once I have tested etc I will post for other people to use.

Beer in post!


Heh... now why would you mess up some nice set based code with a cursor? ;)


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #673904
Posted Wednesday, March 11, 2009 9:05 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 7:27 AM
Points: 35,769, Visits: 32,437
aram_golbaghi (3/11/2009)
hi
i think gsquare's solution is verywell,
works fine


Yep... right up until you dial a 1 or 3 digit country code instead of +44.
Country codes can be from 1 to 3 digits.
City codes can be from 0 to 4 digits.



--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #673906
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse