Test Data Manager

 View Only
  • 1.  Fixed Value masking with Substring Option

    Posted Jun 21, 2018 01:52 PM

    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?

  • 2.  Re: Fixed Value masking with Substring Option

    Posted Jun 22, 2018 02:55 PM



    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 as 'CARD'
    SELECT @card1 = (CONCAT(SUBSTRING(@card1,1,6),'999999',SUBSTRING(@card1,13,4)))
    SELECT @card1 as 'CARD'

  • 3.  Re: Fixed Value masking with Substring Option
    Best Answer

    Posted Jun 25, 2018 05:39 PM



    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
    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
           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:



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



    Hope that helps.