Translate in an Update Statement

Hi all,
running Oracle 9.2.0.1 on Windows.

I'm trying to identify some SSN's in a CLOB and replace them with XXX- -XXXX (to mask out the SSN's).
The SSN's are not consistently at one spot in the field so i can't just go 11 characters in from the beginning or anything. I also have some other numeric values in the field that are similar in format (but not exactly the SSN format) that i want to leave untouched.

Consider:
CREATE TABLE MYRECS (MY_NUMBER INTEGER NOT NULL, MY_DESC CLOB); COMMIT; INSERT INTO MYRECS (MY_NUMBER, MY_DESC) VALUES (1, 'APPLE' || CHR(13)|| CHR(10) || '987-76-1234' || CHR(13)|| CHR(10) || 'Alpha'); INSERT INTO MYRECS (MY_NUMBER, MY_DESC) VALUES (2, '12-G'|| CHR(13)|| CHR(10) || 'BANANA' || CHR(13)|| CHR(10) || '456-78-7890'); INSERT INTO MYRECS (MY_NUMBER, MY_DESC) VALUES (3, '654-44-2589' || CHR(13)|| CHR(10) || 'CHERRY' || CHR(13)|| CHR(10) || 'NUMBER 5 IS ALIVE');
COMMIT; I know i can do something like this to convert numbers to X's: SELECT TRANSLATE(MY_DESC, '0123456789', 'XXXXXXXXXX') AS CHANGED FROM MYRECS; And i have experimented with this to find the first hyphen, back up 3 to the beginning, take 11 chars from there... SELECT TRANSLATE(SUBSTR(MY_DESC, (INSTR(MY_DESC,'-', 1, 1)-3),11), '0123456789', 'XXXXXXXXXX') AS CHANGED FROM MYRECS; But now what? I'm trying to figure out how to formulate the update statement to replace (Translate really) the numbers to XXX-XX-XXXX.

I'm thinking i have to carve up the whole field, take everything before the 11 digit SSN and then everything after the SSN and reassemble  it with the X's in the middle. It's just sort of painful because i am having to INSTR to where the hyphen is, but there are sometimes 6 or 8 hyphens in the field total.

Any shortcuts i am missing? suggestions? Thanks!
Matt Received on Thu Jun 07 2007 - 18:30:10 CDT