On Jun 7, 7:30 pm, matthewf_..._at_yahoo.com wrote:
> 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-
> XX-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 re-
> assemble 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
I wonder if REGEXP_REPLACE is able to do what you want? This is
adapted from the REGEXP_REPLACE example in the SQL Reference for 10g
Release 2:
SELECT
REGEXP_REPLACE(MY_DESC, '([[:digit:]]{3})\-([[:digit:]]{2})\-
([[:digit:]]{4})', 'XXX-YY-ZZZZ') FIX_ME
FROM
MYRECS;
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Received on Thu Jun 07 2007 - 19:41:29 CDT
