Updating records in MSSql Server 2000

[hastx]

There has been a recent area code change and i have to update a contacts table (containing 12,000 contacts) to replace certain phone numbers to reflect the new area code (the area code and number share the same column <555-123-4567>).

To make things worse, the data is not standardized. Some numbers might be (555)123-4567, and some 555.123.4567 and others 555-123-4567.

I don't have any experience with M$ Sql server 2000, but am needing direction on the best way to search and update the area codes in this table. I thought maybe microsoft had a utility for this type of thing.

I think I will have to search and update based on the city of that record.

[mattyblah]

now would be an ideal time to break up and standardize the phone numbers. break it into 2 columns, areacode int and phonenumber int. or you could use char(3), char(7) also. I'd tackle all like phone numbers. you could use this code to assist you:


--use the respective comparer to check numbers like it, and update those a block at a time
declare @num varchar(20)
, @comparer varchar(100)
/*
set @num = '(555)123-4567'
set @comparer = '([0-9][0-9][0-9])[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'
*/
/*
set @num = '555.123.4567'
set @comparer = '[0-9][0-9][0-9].[0-9][0-9][0-9].[0-9][0-9][0-9][0-9]'
*/
/*
set @num = '555-123-4567'
set @comparer = '[0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'
*/


if @num like @comparer
begin
--do something with the matches
end