RE: Transact-sql for changing a telephone number

SSC Guru

Points: 1003857

March 10, 2009 at 1:53 pm

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

--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)