InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 04:59:14 GMT20http://www.sqlservercentral.com/Forums/Topic671598-338-1.aspx[quote][b]aram_golbaghi (3/11/2009)[/b][hr]hii think gsquare's solution is verywell,works fine[/quote]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.Wed, 11 Mar 2009 21:05:30 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic671598-338-1.aspx[quote][b]Spikemarks (3/11/2009)[/b][hr]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![/quote]Heh... now why would you mess up some nice set based code with a cursor? ;)Wed, 11 Mar 2009 20:59:42 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic671598-338-1.aspxhii think gsquare's solution is verywell,works fineWed, 11 Mar 2009 05:54:14 GMTaram_golbaghihttp://www.sqlservercentral.com/Forums/Topic671598-338-1.aspxI 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!Wed, 11 Mar 2009 02:40:29 GMTSpikemarkshttp://www.sqlservercentral.com/Forums/Topic671598-338-1.aspx[quote][b]Spikemarks (3/10/2009)[/b][hr]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 5555555Reformatted+37 (0123) 5555555I think a bit of tweeking needs to be done.[/quote]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. ;)[code]--===== Create and populate a test table. This is NOT part of the solutionDECLARE @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[/code]Heh... send beer... I already have enough pretzels. :PTue, 10 Mar 2009 13:53:23 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic671598-338-1.aspxYeah, best find out all the business rules first.Tue, 10 Mar 2009 07:21:34 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic671598-338-1.aspxAbsolutely 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 5555555Reformatted+37 (0123) 5555555I think a bit of tweeking needs to be done.Tue, 10 Mar 2009 02:50:32 GMTSpikemarkshttp://www.sqlservercentral.com/Forums/Topic671598-338-1.aspxLeave 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.Mon, 09 Mar 2009 21:59:02 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic671598-338-1.aspxThink 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. ;)[code]--===== Create and populate a test table. This is NOT part of the solutionDECLARE @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[/code]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]Mon, 09 Mar 2009 21:47:39 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic671598-338-1.aspxYes 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.Mon, 09 Mar 2009 09:28:25 GMTSpikemarkshttp://www.sqlservercentral.com/Forums/Topic671598-338-1.aspxAfter 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.Mon, 09 Mar 2009 09:26:01 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic671598-338-1.aspxThanks 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 456or +44 (0) 121 430 4992The only constants are the first 7 chars the rest are fluid.+44^(0)^XXX^YYYYYYYXXX 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 helpMon, 09 Mar 2009 09:24:16 GMTSpikemarkshttp://www.sqlservercentral.com/Forums/Topic671598-338-1.aspxI 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.Mon, 09 Mar 2009 09:16:43 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic671598-338-1.aspxTry something like this:[code]declare @Phone varchar(100);select @Phone = '+44 (0) 1908 123 456';select stuff(stuff(replace(@Phone, '(0) ', ''), 5, 0, '('), 10, 0, ')');[/code]Mon, 09 Mar 2009 09:11:54 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic671598-338-1.aspxAlways a zeroThanks for the quick replyMon, 09 Mar 2009 09:07:58 GMTSpikemarkshttp://www.sqlservercentral.com/Forums/Topic671598-338-1.aspxIs the bit removed always a zero, or will it sometimes be something else?Mon, 09 Mar 2009 09:06:14 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic671598-338-1.aspxI need some code to change the phone numbers in a field from one format to another.Original number +44 (0) 1908 123 456Changed number +44 (1908) 123 456Any help is greatly appreciated.Mon, 09 Mar 2009 09:04:48 GMTSpikemarks