currently i am using REGEXP_SUBSTR function to encrypt cc number in a text column of varchar2.
is there a function for oracle 9i that i can use to encrypt the number in any combination to XXX.
thank you
I am assuming by "encrypt" you really mean "redact" ? ie, replace a portion of the card digits with X ?
It just depends on what you want to achieve. Here are some examples:
SQL> create table t ( typ varchar2(10), cc varchar2(50));
Table created.
SQL>
SQL> insert into t values ('Visa','1234-5678-1234-5678');
1 row created.
SQL> insert into t values ('Amex','1234-123456-12345');
1 row created.
SQL>
SQL> select typ,
2 substr(cc,1,instr(cc,'-'))||
3 translate(substr(cc,instr(cc,'-')+1),'0123456789','XXXXXXXXXX')
4 from t;
TYP SUBSTR(CC,1,INSTR(CC,'-'))||TRANSLATE(SUBSTR(CC,INSTR(CC,'-')+1),'0123456789','XXXXXXXXXX')
---------- ----------------------------------------------------------------------------------------------------
Visa 1234-XXXX-XXXX-XXXX
Amex 1234-XXXXXX-XXXXX
SQL>
SQL> select typ,
2 substr(cc,1,instr(cc,'-',-1))||
3 translate(substr(cc,instr(cc,'-',-1)+1),'0123456789','XXXXXXXXXX')
4 from t;
TYP SUBSTR(CC,1,INSTR(CC,'-',-1))||TRANSLATE(SUBSTR(CC,INSTR(CC,'-',-1)+1),'0123456789','XXXXXXXXXX')
---------- ----------------------------------------------------------------------------------------------------
Visa 1234-5678-1234-XXXX
Amex 1234-123456-XXXXX