Example of FDM rule that will mask an email address and keep current domain

Products

• CA Test Data Manager (Data Finder / Grid Tools)

Issue/Introduction

The following sql works to get domain for a email address: 
select regexp_substr ( email, '(@[^.]+\.+[a-zA-Z]{3,}|[a-zA-Z.]{30,})' )from creditcard_e.applicant; 

When I try and exec a sqlfunction it changes to: 
Error executing SQLFUNCTION select SQL:select regexp_substr ( '[email protected]', '( @[^.] + \. + [a - zA - Z]{3 , }|[a - zA - Z.]{30 , } )' )from creditcard_e.applicant ) from dual 
>ORA-00933: SQL command not properly ended 

Is there a way to get rid of the first "select" and the "from dual"? 

The table for email column is the same. I have removed the actual table “creditcard_e.applicant’. The select and the from dual are being added by FDM. 

Function SQL 
app_lastname || regexp_substr ( email, '(@[^.]+\.+[a-zA-Z]{3,}|[a-zA-Z.]{30,})' )) 

Message in execution 
Error executing SQLFUNCTION select SQL:select 'Kruytzer'|| regexp_substr ( '[email protected]', '( @[^.] + \. + [a - zA - Z]{3 , }|[a - zA - Z.]{30 , } )' ) ) from dual 

Do you have an example of an FDM rule that will mask an email address, but keep the current domain information? 

Environment

CA Test Data Manager (TDM)- Fast Data Masker (FDM)

Resolution

This is the resolution we recommend. Use the following CSV masking file options to retain the domain name: FIRST_NAME || '.' || LAST_NAME || '@' || SUBSTR( EMAIL , INSTR( EMAIL , '@') + 1) 

 

<Please see attached file for image>

src="/servlet/servlet.FileDownload?file=0150c000004AKgJAAW" alt="email csv screenshot.png" width="1657" height="121">

Both parameters to SQLFUNCTION are incorrect with respect to FDM: 
1) It cannot just be a sub select from another table. 
2) There should be no select or from clause.
Try:
APP_LASTNAME || REGEXP_SUBSTR(EMAIL, ......) WITHOUT THE SELECT AND WITHOUT FROM CREDITCARD_E.APPLICANT 

 

As a another quick workaround, we suggest the following with a fixed domain name:

<Please see attached file for image>

src="/servlet/servlet.FileDownload?file=0150c000004AKgKAAW" alt="workaround s1.png" width="943" height="316">

<Please see attached file for image>

src="/servlet/servlet.FileDownload?file=0150c000004AKgLAAW" alt="workaround s2.png" width="943" height="311">

<Please see attached file for image>

src="/servlet/servlet.FileDownload?file=0150c000004AKgMAAW" alt="workaround s3.png" width="636" height="332">

You could also write a custom function that retains the string after the @ sign, and then concat with some other masked fields (i.e. first and last name). Refer to the following for how to search for a character: https://stackoverflow.com/questions/8646441/how-can-i-get-the-text-before-and-after-the-dash 

Additional Information

If you experience any further issues, please open a support case by going to https://support.broadcom.com