Changing some number in a SQL DB

[Garling]

I have some phone numbers in a SQL DB that has the incorrect area code, the number format is 3339998888 where '333' is the area code. I need to change the first 3 digits of the number to '228', how can I do this using a SQL query?

Thanks

 

[r937]

what is the datatype of the column?

r937.com | rudy.ca

 

[Garling]

The data type is 'char'.

 

[r937]

UPDATE daTable
   SET phone = '228' + SUBSTRING(phone,4,937)
 WHERE LEFT(phone,3) = '333'

r937.com | rudy.ca

 

[gmmastros]

You can do better. Make it sargable.

UPDATE daTable
   SET phone = '228' + SUBSTRING(phone,4,937)
 WHERE phone [!]Like[/!] '333[!]%[/!]'

If there is an index on this table, the LIKE query will use it. For more info about this.... [google]sql server sargable[/google]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[r937]

sorry

i forgot that sql server cannot figure out the behaviour of LEFT with an indexed character column

;-)

r937.com | rudy.ca

 

[dgillz]

Can someone explain the 937 argument?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[ESquared]

Just FYI... note that the reason the LIKE is sargable is because the engine converts it to a BETWEEN (or >= and <= ranges, with some variation, I've seen the end ranges slightly larger):

WHERE phone Like '333%'
-->
WHERE
   phone BETWEEN '333' AND '334' -- now can use an index seek
   AND phone Like '333%'

And the 4 in 334 might be the last character of the collation instead. But you get the idea.

937 is part rudy's login name. You just needed a number longer than the length of the string to ensure getting all characters. 2 wouldn't have worked well as you'd have missed some of the phone number. 100 or 500 or 8000 all work fine. More than 8000 is a problem for anything but varchar(max) data type in SQL 2005 and up, but you'd have to test it to be sure because I'm not.