SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 How to replace a "+7" to "8" in phone number
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

krainov
Yak Posting Veteran

57 Posts

Posted - 09/28/2012 :  05:09:01  Show Profile  Reply with Quote
Hi there.
How to find the "+ 7 (495)" or "+ 7 (499)" or "+7(495)" or "+7(499)" or "+7 (495)" or "+7 (499)" in the varchar field and replace it with "8"? I need to replace the international code in phone numbers with russian formatting for the russian locales of all site materials/publications on the pages.

Thanks!

stepson
Constraint Violating Yak Guru

Romania
400 Posts

Posted - 09/28/2012 :  06:10:04  Show Profile  Reply with Quote
Hi;

replace(replace(field ,' ',''),'+7(495)','8')

replace(replace(field ,' ',''),'+7(499)','8')

first replace(field ,' ','') -get rid of then blank/space char
then replace your text

S
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 09/28/2012 :  06:10:05  Show Profile  Reply with Quote
UPDATE TableName SET ColumnName = REPLACE ( ColumnName, '+ 7', '8')

--
Chandu
Go to Top of Page

krainov
Yak Posting Veteran

57 Posts

Posted - 09/28/2012 :  06:27:40  Show Profile  Reply with Quote
Thanks bandi and stepson! It works fine! )
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 09/28/2012 :  06:31:08  Show Profile  Reply with Quote
Welcome....


You can also update it with single update statement


UPDATE TableName SET Col =
case when replace( col, ' ', '') = '7+(495)'
then REPLACE ( ColumnName, '+7(495)', '8')
when replace( col, ' ', '') = '7+(499)'
then REPLACE ( ColumnName, '+7(495)', '8')
else col end,


--
Chandu

--
Chandu
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000