Help updating a phone field(Area code change.) 2

[redsand]

I am trying to update the first 3 positions in a phone field due to a area code change. I need help on how to parse the first 3 positions in the field and do a replace
Here is what I was going to try
Update tbl
set phone = replace(left(phone,3),'616','927)

The problem that I see with it is that it will update but it will update with just the 927, it need bring with it the rest of the number

Anyhelp would be greatly appreciated!!!

 

[dcusick]

Redsand,

Just concatenate the last characters to the end of your replace string as follows...

UPDATE tbl
SET phone = REPLACE(LEFT(phone, 3), '616', '927') + RIGHT(phone, LEN(phone) - 3)

What this will do is replace the first 3 characters and then concatenate the rest of the phone number. Hope this helps.

Doug

 

[r937]

if you only have 927 phone numbers, then this can be simplified to

update tbl
set phone = '927' + RIGHT(phone,LEN(phone)-3)

however, if you have other area codes in there, then the previous suggestion involving REPLACE will update every row in the table, which is not all that efficient, eh?

so how about

update tbl
set phone = '927' + RIGHT(phone, LEN(phone) - 3)
where Left(phone,3)='616'

rudy

http://rudy.ca/