Home » RDBMS Server » Server Administration » cleaning phone-numbers column with explicit cursor
| cleaning phone-numbers column with explicit cursor [message #371259] |
Sun, 24 September 2000 15:30 |
Natalia
Messages: 4
Registered: September 2000
|
Junior Member |
|
|
Here is my question. I have a DB table (say - "myuser") and columns like "userID" and "phoneNumber". In the actual DB there are a lot of records with not appropriate format. I need to make everything looking like: "***-***-****". I am a beginner and need some help.
here my code below but it is formatting everything using only first number in the table. Something I did wrong with a loop.
Please reply on my nzolotar@hotmail. Thanks. Natalia.
----------------------------------------------------
DECLARE
goodPhone myuser.workPhoneNumber%TYPE;
finalPhone myuser.workPhoneNumber%TYPE;
phone_length NUMBER(2);
counter NUMBER(2) :=1;
CURSOR cursor_name IS
SELECT workPhoneNumber, userOID
FROM myuser
WHERE workPhoneNumber IS NOT NULL;
BEGIN
FOR c_work IN cursor_name LOOP --go through all values in the cursor (implicit open and fetch occur)
--CLEANING THE DB****
phone_length := LENGTH (c_work.workPhoneNumber);
WHILE counter <= phone_length LOOP
--go through all numbers in the phone****
IF SUBSTR(c_work.workPhoneNumber,counter,1) NOT IN
('a','b','c','d','e','g','h','i','j','k','l','m','n','o','p','r','s','t','u','v','w','x','y','z','*','!','@','#','$','%','^','&','(',')','.',',','?','/','[[',']]','{','}','-')
THEN
goodPhone := goodPhone || SUBSTR(c_work.workPhoneNumber,counter,1);
END IF;
--INSERTING THE DASHES****
IF LENGTH(goodPhone) = 10 THEN
finalPhone := substr(goodPhone, 1, 3) || '-' || substr(goodPhone, 4, 3) || '-' || substr(goodPhone, 7, 4);
ELSIF LENGTH(finalPhone) < 10 THEN
finalPhone := '000-000-000';
ElSIF SUBSTR (goodPhone,1,1) = '1' THEN
finalPhone := substr(goodPhone, 2, 3) || '-' || substr(goodPhone, 5, 3) || '-' || substr(goodPhone, 8, 4);
END IF;
counter := counter + 1;
END LOOP;
--UPDATING the table****
UPDATE myuser
SET workPhoneNumber = finalPhone
WHERE userOID = c_work.userOID;
END LOOP;--implicit close occurs
END;
/
------------------------------------------------
original table:
USEROID WORKPHONENUMBER
--------- ------------------
1 1-954-771-3622
2 none
3 305-121-4567
4 1(718)367-4568
5 1.222.333.4444
6 1892787174187
7 01222
8 954-444-5550 x.23
8 rows selected.
after my code:
USEROID WORKPHONENUMBER
--------- ----------------
1 954-771-3622
2 954-771-3622
3 954-771-3622
4 954-771-3622
5 954-771-3622
6 954-771-3622
7 954-771-3622
8 954-771-3622
8 rows selected.
|
|
|
|
Goto Forum:
Current Time: Sat Apr 20 09:01:49 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
