RE:PHONE NUMBERS UPDATE

[Marshall]

I am trying to update a bunch of phone numbers in a sql 6.5 db.
The phone numbers are in the following formate.
(aaa)-xxx-cccc
I need to update xxx to yyy. Has anyone done this before.
Please help.


Thanks in advance

Ken

[Ivo]

So, suppose you have a table 'transfer' with columns xxx, yyy
UPDATE a
SET num = SUBSTRING(num, 1, 6)+b.yyy+SUBSTRING(num, 10, 5)
FROM <your table> a, transfer b
WHERE SUBSTRING(a.num, 7, 3) = b.xxx

-- where &#39;num&#39; is name of your column with phone numbers.
If there is only one pair of xxx, yyy you don&#39;t need to create table transfer, of course.


------------
Marshall at 8/14/01 10:26:17 PM

I am trying to update a bunch of phone numbers in a sql 6.5 db.
The phone numbers are in the following formate.
(aaa)-xxx-cccc
I need to update xxx to yyy. Has anyone done this before.
Please help.


Thanks in advance

Ken

[Marshall]

Ivo,
Thanks for your respond. However,i am getting lots of error messages with this script. Maybe my question is not clear enough.

I need to update a table call &#34;SALES&#34;.
The column name is call &#34;PHONE&#34;
The phone numbers are in these formate==> (AAA)- BBB - CCCC
(AAA) is the area code.
BBB is the set of numbers following the area code.
CCCC represent the last four numbers.

I need to update the BBB to ZZZ in order for my phone number to look like this
(AAA) - ZZZ-CCCC

Please help.


------------
Ivo at 8/15/01 11:14:20 AM

So, suppose you have a table &#39;transfer&#39; with columns xxx, yyy
UPDATE a
SET num = SUBSTRING(num, 1, 6)+b.yyy+SUBSTRING(num, 10, 5)
FROM <your table> a, transfer b
WHERE SUBSTRING(a.num, 7, 3) = b.xxx

-- where &#39;num&#39; is name of your column with phone numbers.
If there is only one pair of xxx, yyy you don&#39;t need to create table transfer, of course.


------------
Marshall at 8/14/01 10:26:17 PM

I am trying to update a bunch of phone numbers in a sql 6.5 db.
The phone numbers are in the following formate.
(aaa)-xxx-cccc
I need to update xxx to yyy. Has anyone done this before.
Please help.


Thanks in advance

Ken

[Ivo]

Hi Marshall
You don&#39;t write here what error messages you get with a query, but try exactly this one (I suppose there is a space on 7th and 11th position of PHONE and that PHONE is type of char or varchar:
UPDATE SALES
SET PHONE = SUBSTRING(PHONE, 1, 7) + &#39;ZZZ&#39; + SUBSTRING(PHONE, 11, 7)
WHERE SUBSTRING(PHONE, 8, 3) = &#39;BBB&#39;


------------
Marshall at 8/16/01 8:33:41 PM

Ivo,
Thanks for your respond. However,i am getting lots of error messages with this script. Maybe my question is not clear enough.

I need to update a table call &#34;SALES&#34;.
The column name is call &#34;PHONE&#34;
The phone numbers are in these formate==> (AAA)- BBB - CCCC
(AAA) is the area code.
BBB is the set of numbers following the area code.
CCCC represent the last four numbers.

I need to update the BBB to ZZZ in order for my phone number to look like this
(AAA) - ZZZ-CCCC

Please help.


------------
Ivo at 8/15/01 11:14:20 AM

So, suppose you have a table &#39;transfer&#39; with columns xxx, yyy
UPDATE a
SET num = SUBSTRING(num, 1, 6)+b.yyy+SUBSTRING(num, 10, 5)
FROM <your table> a, transfer b
WHERE SUBSTRING(a.num, 7, 3) = b.xxx

-- where &#39;num&#39; is name of your column with phone numbers.
If there is only one pair of xxx, yyy you don&#39;t need to create table transfer, of course.


------------
Marshall at 8/14/01 10:26:17 PM

I am trying to update a bunch of phone numbers in a sql 6.5 db.
The phone numbers are in the following formate.
(aaa)-xxx-cccc
I need to update xxx to yyy. Has anyone done this before.
Please help.


Thanks in advance

Ken