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 Monday, March 9, 2009 9:04 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, February 29, 2012 7:33 AM
Points: 19, Visits: 60
I need some code to change the phone numbers in a field from one format to another.

Original number +44 (0) 1908 123 456
Changed number +44 (1908) 123 456

Any help is greatly appreciated.
Post #671598
Posted Monday, March 9, 2009 9:06 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, December 18, 2014 9:58 AM
Points: 13,872, Visits: 9,600
Is the bit removed always a zero, or will it sometimes be something else?

- 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 #671601
Posted Monday, March 9, 2009 9:07 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, February 29, 2012 7:33 AM
Points: 19, Visits: 60
Always a zero

Thanks for the quick reply
Post #671604
Posted Monday, March 9, 2009 9:11 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, December 18, 2014 9:58 AM
Points: 13,872, Visits: 9,600
Try something like this:

declare @Phone varchar(100);

select @Phone = '+44 (0) 1908 123 456';

select stuff(stuff(replace(@Phone, '(0) ', ''), 5, 0, '('), 10, 0, ')');



- 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 #671608
Posted Monday, March 9, 2009 9:16 AM


SSC-Dedicated

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

Group: Administrators
Last Login: Yesterday @ 3:11 PM
Points: 31,368, Visits: 15,837
I might also be sure that the spacing is there. If you might have more than one space, I'd consider using charindex/patindex to find the parens and go from there.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #671618
Posted Monday, March 9, 2009 9:24 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, February 29, 2012 7:33 AM
Points: 19, Visits: 60
Thanks for that it does what I want for the example phone number.

I also have other phone numbers though they might be
+44 (0) 1908 123 456
or
+44 (0) 121 430 4992

The only constants are the first 7 chars the rest are fluid.

+44^(0)^XXX^YYYYYYY

XXX could be 3 or 4 or 5 chars and YYYYYYY is the remainder of the number.

When I get a moment I will have a play to see if I can get something to work just am a bit busy at the moment to spend a lot of time on it.

Again thanks for your help


Post #671629
Posted Monday, March 9, 2009 9:26 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, December 18, 2014 9:58 AM
Points: 13,872, Visits: 9,600
After you remove the "(0)", does the second set of numbers always get parens around it? If so, then Steve's suggestion of using charindex/patindex to find the spaces around that and add parens to them will work.

- 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 #671632
Posted Monday, March 9, 2009 9:28 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, February 29, 2012 7:33 AM
Points: 19, Visits: 60
Yes I think that will be the way as I am basically trying to get the area code into the brackets (removing the 0) leaving the rest of the phone number untouched.
Post #671637
Posted Monday, March 9, 2009 9:47 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
Think not of what to do to the row... think of what to do to the column... and, you don't have to do it all at once... Divide'n'Conquer. ;)

--===== 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) ','(') AS PartialFormat FROM @PhoneNumbers
) d

Yields...
Original Reformatted
-------------------- ------------------
+44 (0) 1908 123 456 +44 (1908) 123 456
+44 (0) 121 430 4992 +44 (121) 430 4992


--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 #672144
Posted Monday, March 9, 2009 9:59 PM


SSC-Dedicated

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

Group: Administrators
Last Login: Yesterday @ 3:11 PM
Points: 31,368, Visits: 15,837
Leave it to Jeff to come up with a solution. It's a good one as well.

You want to think pattern, working all rows at once from the pattern perspective, as Jeff has done, and letting the server do the hard work across all rows.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #672147
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse