Alternative for regexp_replace for BIGINT-CodePudding

I'm quite new at programming with Oracle and DB2 and have a question. I need to mask a field that has a BIGING as datatype. But when i tried to execute a query with regexp_replace, i have this error line SQLCODE=-420, SQLSTATE=22018. Is there a alternative for a regexp_replace for BIGING.

Thanks a lot!

CodePudding user response：

You can 'mask' integers by replace all digits except first and last by zeroes using next code (Oracle):

select 
    N, -- source number
    FLOOR(N/POWER(10, FLOOR(LOG(10, N)))) * POWER(10, FLOOR(LOG(10, N)))   MOD(N, 10) MASKED
from a;

run sql online

CodePudding user response：

Depending on the platform and version of Db2, you might consider using CREATE MASK if available. That would ensure the data is always masked without needing to do it in every application.

A quick search seems to indicate the Oracle also has similar support but they call it redaction. Masking in oracle seems to be tied into subsetting and exporting data from production to DEV/TEST.

Do you really need a solution for both RDBMs?

And if you really want to roll your own, you need to provide some examples of the masked value you want returned.