SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Transact-sql for changing a telephone number


Transact-sql for changing a telephone number

Author
Message
Spikemarks
Spikemarks
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
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.
GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23451 Visits: 9730
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
Spikemarks
Spikemarks
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 60
Always a zero

Thanks for the quick reply
GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23451 Visits: 9730
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
Steve Jones
Steve Jones
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: Administrators
Points: 62495 Visits: 19103
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
My Blog: www.voiceofthedba.com
Spikemarks
Spikemarks
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
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
GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23451 Visits: 9730
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
Spikemarks
Spikemarks
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
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.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86031 Visits: 41095
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. Wink

--===== 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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Steve Jones
Steve Jones
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: Administrators
Points: 62495 Visits: 19103
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
My Blog: www.voiceofthedba.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search