Fixed Value masking with Substring Option

[Shrini_TSYS]

I am trying to Mask a 16 Digit Credit Card number with the 1st 6 and Last 4 digits as same and the middle 6 digits to be replaced with a Fixed Value say '999999'.

 

I need to do this on SQL Server Database for a VARCHAR Field storing numeric values using Transformation Map (Not FDM).

 

I see that with FIXED value replacement, the Substring option does not work. Is there any Function available to implement above requirement?

[Sean-K-CA]

Srini,

 

I believe that the "Fixed Value" column can also run Stored Procedures within the DB. This doesn't appear to be in the documentation at all.

 

Assuming that we can call Stored Procedures or other SQL syntax from Fixed Value, I'm not 100% sure what the requirements are for input and output.

 

I'm looking into this further and will update accordingly.

 

 

Some sample/POC TSQL that more or less gets the job done (using the sample creditcard DB provided in the repo kit):

 

DECLARE @card1 varchar(16);
SELECT @card1 = (SELECT TOP 1 CARD_NO FROM CARD_ACCOUNT);
SELECT @card1 as 'CARD'
SELECT @card1 = (CONCAT(SUBSTRING(@card1,1,6),'999999',SUBSTRING(@card1,13,4)))
SELECT @card1 as 'CARD'

[Sean-K-CA]

Srini,

 

Here's the explanation that I received from the dev team:

 

----

Basically, whatever is in the fixed value column gets called in the SQL select for either the datapump ,CTAS or update scripts.
So for example if it is a fixed value of ‘FIXED’ then the SQL for a table with col1, col2 and col3 looks like
SELECT COL1,
       COL2,
       ‘FIXED’
FROM table

 

If there is a user defined function that can be called from SQL, then this can be added in the fixed value column instead and it will be added to the SQL automatically.
For example if I have a function ADD that adds the values of 2 columns, then I would need to put this in the fixed value column together with its arguments, so ADD(COL1, COL2)
Then the SQL becomes
SELECT COL1,
       COL2,
       ADD(COL1, COL2)
FROM table

 

The update becomes:
UPDATE table SET COL3 = ADD(COL1, COL2)

----

 

In your case, standard SQL should do the trick as noted previously:

CONCAT(SUBSTRING(CARD_NO,1,6),'999999',SUBSTRING(CARD_NO,13,4))

 

Replace CARD_NO with your column name and this should mask as desired.

 

 

Hope that helps.