Hi Marshall
You don'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) + 'ZZZ' + SUBSTRING(PHONE, 11, 7)
WHERE SUBSTRING(PHONE, 8, 3) = 'BBB'


------------
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 "SALES".
The column name is call "PHONE"
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 '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