REGEXP_REPLACE Credit Card Number with 'X' in obiee report

Steelbird

Hi @Team,

I had a requirement where I need to replace the Credit Card number with 4567123789762344 to XXXXXXXXXXXX2344.

I am using this query in Back end data base where I Can accomplish the task but how can we implement it in reporting.

Sql Query:

Select REGEXP_REPLACE(SUBSTR('1234567891234440',1,'1234567891234440'-4), '[0-9]', 'X')||SUBSTR('1234567891234440',-4)  FROM DUAL;

Select REGEXP_REPLACE(SUBSTR(CREDIT_CARD_NUM,1,CREDIT_CARD_NUM-4), '[0-9]', 'X')||SUBSTR(CREDIT_CARD_NUM,-4),

CREDIT_CARD_NUM FROM W_SALES_F;

In Reporting I am using below formula.

EVALUATE('REGEXP_REPLACE(SUBSTR(%1,1,%1-4),''[0-9]'','X') || SUBSTR(%1,-4)',"Sales Details"."CC Number")

It is giving error 'X' invalid Identifier.

Thank you,

Steel.

Gianni Ceresa

You are mixing ' and " in your formula, breaking (ending) the string too early. Correct your usage of ' and " and give it another try.

Steelbird

I have used below formula in report.

EVALUATE('REGEXP_REPLACE(SUBSTR(%1,1,%1-4),''[0-9]'',''X'')||SUBSTR(%1,-4)',"Order Details"."Credit Card Number") - syntax is working fine.

But it is populating data as '0.00' in front end report where as If I executed the back end code in database it is giving expected results as XXXXXXXXXXXX2344.

Jerry Casey

Hi Steel,

Would it be simpler to use string functions, as in: REPEAT('X',(LENGTH("cc#")-4))||RIGHT("cc#",4)?

Jerry

Gianni Ceresa

From a logical point of view @Jerry Casey option is better: why do you want to bother the system with EVALUATE when the native OBIEE functions are enough?

From a business point of view, that's something you must have directly in your database. You can't trust the front end, every single analysis to always obfuscate credit card numbers.

If in your data you have even a single European customer, what you are doing is actually illegal based on the GDPR regulations, because data isn't hidden but still freely accessible being just done in the analysis itself.

The minimum would be to do it in the RPD, but you better think at doing it in the DB so that everybody has the same obfuscated column.

Jerry Casey

Thanks for the lesson! I don't deal with much personal identification data.

Jerry