sql server -replace characters in a column in an entire table

louise_8
louise_8 used Ask the Experts™
on
Hi,

Im looking to replace characters in the phone number columns in an entire table

eg replace any phone number starting with '+614' or '+61 4' to '04'

update customer
set vphone = ??
where vPhone like '+614%' or '+61 4%'

thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Steve WalesSenior Database Administrator
Commented:
Books Online has info on the built in functions, in this case you're looking for replace:
http://technet.microsoft.com/en-us/library/ms186862.aspx

update customer 
set vphone = replace(vphone, '+614', '04')
where vPhone like '+614%';

update customer 
set vphone = replace(vphone, '+61 4', '04')
where vPhone like '+61 4%';

Open in new window


Since there are two match strings of different lengths, I think you have to do 2 updates ... but not 100% sure on that :)

I don't think SQL Server has a REGEXP replace function.
Senior .Net Developer
Commented:
You can do it in one statement:
update customer 
set vphone = replace(replace(vphone, '+614', '04'),'+61 4', '04)
where vPhone like '+614%' or vphone like '+61 4%';

Open in new window

Author

Commented:
thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial