March 9, 2009 at 9:04 am
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.
March 9, 2009 at 9:06 am
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
March 9, 2009 at 9:07 am
Always a zero
Thanks for the quick reply
March 9, 2009 at 9:11 am
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
March 9, 2009 at 9:16 am
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.
March 9, 2009 at 9:24 am
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
March 9, 2009 at 9:26 am
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
March 9, 2009 at 9:28 am
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.
March 9, 2009 at 9:47 pm
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...
[font="Courier New"]Original Reformatted
-------------------- ------------------
+44 (0) 1908 123 456 +44 (1908) 123 456
+44 (0) 121 430 4992 +44 (121) 430 4992[/font]
--Jeff Moden
Change is inevitable... Change for the better is not.
March 9, 2009 at 9:59 pm
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.
March 10, 2009 at 2:50 am
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.
March 10, 2009 at 7:21 am
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
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 bracketsOriginal
+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
Change is inevitable... Change for the better is not.
March 11, 2009 at 2:40 am
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!
March 11, 2009 at 5:54 am
hi
i think gsquare's solution is verywell,
works fine
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply