-
RE:PHONE NUMBERS UPDATE
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
-
RE:PHONE NUMBERS UPDATE (reply)
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 'num' is name of your column with phone numbers.
If there is only one pair of xxx, yyy you don'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
-
RE:PHONE NUMBERS UPDATE
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 'num' is name of your column with phone numbers.
If there is only one pair of xxx, yyy you don'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
-
RE:PHONE NUMBERS UPDATE (reply)
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 'num' is name of your column with phone numbers.
If there is only one pair of xxx, yyy you don'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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|