# CA Test Data Manager 4.9.1

The ACCT_01 function substitutes digits in the original value with appropriate digits present in Parm1. Each digit in the original value represents a corresponding position in Parm1. And, the digit present at that position in Parm1 is used to replace the digit in the original value.
4-9
2
2
ACCT_01
The ACCT_01 function substitutes digits in the original value with appropriate digits present in Parm1. Each digit in the original value represents a corresponding position in Parm1. And, the digit present at that position in Parm1 is used to replace the digit in the original value.
If Parm1 is not provided, the default value of
2749503168
is used. Regardless of the value for Parm1, the last digit in the masked number is always set to
9
.
The length of the Parm1 value must always include ten digits.
Parameters
• Parm1 (Optional)
Specifies the digits to use for replacing the original value.
Applies to:
Numeric and Character
Example:
Consider the following example to understand this function:
Original value:
7564936295
You want to mask this original value with the value provided in Parm1.
Value in Parm1:
6721843283
The following tables shows positions of digits in Parm1:
 Position 0 1 2 3 4 5 6 7 8 9 Value 6 7 2 1 8 4 3 2 8 3
7
in the original value with a digit from Parm1, the function identifies that the digit to be masked in the original value is
7
. Using that digit as a reference, the function finds the position
7
in Parm1. It then finds the digit present at the position
7
in Parm1. In this case, the digit at position
7
is
2
. So, the function replaces digit
7
in the original value with digit
2
. The original number now becomes
2
56493629
9
.
Similarly, the function takes the next digit in the original value, which is digit
5
. The function identifies the digit present at position
5
in Parm1, which is
4
. It then replaces digit
5
in the original value with digit
4
. The original number now becomes
24
6493629
9
.
By following the same logic, the final masked number becomes
2438313239
. The last digit is always set to 9 in the masked value.
The following table shows the usage:
 Table Column Function Parm1 Parm2 Parm3 ACCOUNT ACCOUNT_NO ACCT_01 6721843283
Note:
For values that include both digits and characters (for example,
abd123xy28
), the function masks only digits. The function does not mask characters.
The ADD function adds a fixed value specified in Parm1 to the original value. The ADD function also adds the fixed value specified in Parm1 to dates in a character field.
Note:
In a Microsoft SQL Server environment, the ADD function does not add the user-specified number of days to dates. For example, consider a scenario where the Hire_date column contains the value
2001-07-07
(YYYY-MM-DD format). In this case, if you use the ADD function with the value as
10
days, the function does not add
10
sqljdbc_4.0.2206.100_enu.exe
sqljdbc4.jar
file from the extracted location to the
%FDM%/lib
folder. Now, if you use the ADD function, it works as expected in your Microsoft SQL Server environment.
Parameters
• Parm1
Specifies the fixed value to add.
Applies to:
Numeric, Character, and Date
Example:
The
column in the table
ORDERS
has
5
added to the existing value. The following table shows the usage:
 Table Column Function Parm1 Parm2 Parm3 KeepNull ORDERS SHIP_TO_ADDRESS_ID ADD 5 N
The following are the original and masked values for this example:
• 101
to
106
• 102
to
107
• 103
to
108
The ADDDAYS function adds a random number of days between 1 and the value specified in Parm1 to the existing value.
Parameters
• Parm1
Specifies the number of days to add.
Applies to:
Date and Character
Example:
The
TEST_DATE
column in the table
CREDIT_CARD
1
and
14
days to the existing value. The following table shows the usage:
 Table Column Function Parm1 Parm2 Parm3 Parm4 CREDIT_CARD TEST_DATE ADDDAYS 14
The following are the original and masked values for this example:
• 1988-01-07
to
1988-01-19
• 1982-03-01
to
1982-03-13
• 1962-05-06
to
1962-05-18
• 1963-01-21
to
1963-02-02
The ADDPERCENT function adds a fixed percentage value provided in Parm1 to the original value.
Parameters
• Parm1
Specifies the percentage value to add.
Applies to:
Numeric
Example:
The
PRICE
column in the table
ORDERS
has
10%
added to the existing value. The following table shows the usage:
 Table Column Function Parm1 Parm2 Parm3 Parm4 KeepNull ORDERS PRICE ADDPERCENT 10 N
The following are the original and masked values for this example:
• 100.00
to
110.00
• 200.00
to
220.00
• 300.00
to
330.00
• 400.00
to
440.00
The ADDRANDOM function adds a random value between Parm1 and Parm2 to the existing value.
Parameters
• Parm1
Specifies the minimum value to use.
• Parm2
Specifies the maximum value to use.
Applies to:
Numeric
Example:
The
UNIT_PRICE
column in the table
ORDER_ITEMS
has a value between
-4
and
4
added to the existing value. The following table shows the usage:
 Table Column Function Parm1 Parm2 Parm3 Parm4 KeepNull ORDER_ITEMS UNIT_PRICE ADDRANDOM -4 4 N
The following are the original and masked values for this example:
• 112
to
114
• 107
to
104
• 109
to
107
• 115
to
117
The ADDRANDOMDAYS function adds a random number of days between Parm1 and Parm2 to the existing value. The function does not mask the bad data. Example of bad data includes any data that does not match the supplied date format. For example, date format is
YYYYMMDD
and the data to be masked is
010101
.
Parameters
• Parm1
Specifies the minimum value to use.
• Parm2
Specifies the maximum value to use.
Applies to:
Date
Example:
The
TEST_DATE
column in the table
CREDIT_CARD
has a value between
10
and
100
days added to the existing value. The following table shows the usage:
 Table Column Function Parm1 Parm2 Parm3 Parm4 CREDIT_CARD TEST_DATE ADDRANDOMDAYS 10 100
The following are the original and masked values for this example:
• 2015-01-02
to
2015-03-25
• 2004-08-28
to
2004-11-26
• 2013-02-02
to
2013-03-25
The ADDRANDOMHOURS function adds a random number of hours between Parm1 and Parm2 to the existing value.
Parameters
• Parm1
Specifies the minimum value to use.
• Parm2
Specifies the maximum value to use.
Applies to:
Date
Example:
The
CREATION_DATE
column in the table
SHIPPING_OPTIONS_BASE
has a value between
6
and
9
hours added to the existing value. The following table shows the usage:
 Table Column Function Parm1 Parm2 Parm3 Parm4 SHIPPING_OPTIONS_BASE CREATION_DATE ADDRANDOMHOURS 6 9
The ADDRANDOMMINUTES function adds a random number of minutes between Parm1 and Parm2 to the existing value.
Parameters
• Parm1
Specifies the minimum value to use.
• Parm2
Specifies the maximum value to use.
Applies to:
Date
Example:
The
CREATION_DATE
column in the table
SHIPPING_OPTIONS_BASE
has a value between
30
and
45
minutes added to the existing value. The following table shows the usage:
 Table Column Function Parm1 Parm2 Parm3 Parm4 SHIPPING_OPTIONS_BASE CREATION_DATE ADDRANDOMMINUTES 30 45
The ADDRANDOMSECONDS function adds a random number of seconds between Parm1 and Parm2 to the existing value.
Parameters
• Parm1
Specifies the minimum value to use.
• Parm2
Specifies the maximum value to use.
Applies to:
Date
Example:
The
CREATION_DATE
column in the table
OPTIONS_BASE
has a value between
4
and
13
seconds added to the existing value. The following table shows the usage:
 Table Column Function Parm1 Parm2 Parm3 Parm4 OPTIONS_BASE CREATION_DATE ADDRANDOMSECONDS 4 13
The ADDRANDOMYEARS function adds a random number of years between Parm1 and Parm2 to the existing value.
Parameters
• Parm1
Specifies the minimum value to use.
• Parm2
Specifies the maximum value to use.
Applies to:
Date and Numeric
Example:
The
CREATION_DATE
column in the table
OPTIONS_BASE
has a value between
6
and
9
years added to the existing value. The following table shows the usage:
 Table Column Function Parm1 Parm2 Parm3 OPTIONS_BASE CREATION_DATE ADDRANDOMYEARS 6 9
AES128DECRYPT
The DECRYPT function creates a decrypted version of a column based on the key in Parm1. It uses the decryption algorithm DES with key length 128.
Parameters
• Parm1
Specifies the encryption key to use.
Applies to:
Character
AES128ENCRYPT
The ENCRYPT function creates an encrypted version of a column based on the key in Parm1. It uses the encryption algorithm AES with key length 128.
Parameters
• Parm1
Specifies the encryption key to use.
Applies to:
Character
Note:
The encrypted version is longer than the original value. So ensure that the column width can accommodate the new value.
AES256DECRYPT
The DECRYPT function creates a decrypted version of a column based on the key in Parm1. It uses the decryption algorithm DES with key length 256.
Parameters
• Parm1
Specifies the encryption key to use.
Applies to:
Character
AES256ENCRYPT
The ENCRYPT function creates an encrypted version of a column based on the key in Parm1. It uses the encryption algorithm AES with key length 256.
Parameters
• Parm1
Specifies the encryption key to use.
Applies to:
Character
Note:
The encrypted version is longer than the original value. So ensure that the column width can accommodate the new value.
AMEXCARD
The AMEXCARD function generates a random American Express (AMEX) credit card number.
Parameters:
None
Applies to:
Character and Numeric
Example:
Generates an AMEX number, such as
345268721090015
. The following table shows the usage:
 Table Column Function Parm1 Parm2 Parm3 CUSTOMER CARD AMEXCARD
CHARHASH
The CHARHASH function converts a character hashed value from the input. Parm1 must be set to the method MD2, MD5, SHA-1, SHA-256, SHA-384, or SHA-512.
Parameters
• Parm1
Specifies the method (algorithm) to use.
Applies to:
Character
Example:
The value
COUP
in the
DISCOUNT_TYPE_CODE
9ccff020641cc1a68770161075cf33
. The following table shows the usage:
 Table Column Function Parm1 Parm2 Parm3 DISCOUNT_BASE DISCOUNT_TYPE_CODE CHARHASH SHA-512
CHECKRUT
The CHECKRUT function generates a Chilean Social Security Number (RUT) based on an existing value in another column in the table.
Parameters
• Parm1
Specifies the name of another column in the same table that contains two or more RUT ardnumbers.
Applies to:
Character
Example:
The following table shows the usage:
 Table Column Function Parm1 Parm2 Parm3 CUSTOMER NUMBER CHECKRUT ORIGINAL
COMBINEVALS
Note:
This function appears in the Fast Data Masker UI only when Old Style Mapper is used (
Configuration
,
Old Style Mapper
).
The COMBINEVALS function combines all subsequent masking for the column.
Parameters:
Parm1, Parm2, Parm3, and Parm4 (Optional)
Applies to:
Character
Example:
The following table shows the usage:
 Table Column Function Parm1 Parm2 Parm3 Parm4 KeepNulls DateFor C_BO_ELCTRNC_ADDR2 ELCTRNC_TXT HASHLOV EMAIL PROVIDERS 1 Y C_BO_ELCTRNC_ADDR2 ELCTRNC_HIDE_SRC_TXT COMBIBEVALS Y C_BO_ELCTRNC_ADDR2 ELCTRNC_HIDE_SRC_TXT HASHLOV FEMALENAMES 1 Y C_BO_ELCTRNC_ADDR2 ELCTRNC_HIDE_SRC_TXT FIXED - Y C_BO_ELCTRNC_ADDR2 ELCTRNC_HIDE_SRC_TXT HASHLOV MALENAMES 1 Y C_BO_ELCTRNC_ADDR2 ELCTRNC_HIDE_SRC_TXT FIXED @ C_BO_ELCTRNC_ADDR2 ELCTRNC_HIDE_SRC_TXT HASHLOV EMAIL PROVIDERS 1 Y
CONCAT
The CONCAT function concatenates values in Parm1 to Parm4 (can be column names or literal values).
Note:
Fast Data Masker tests to see if the value entered is a column in the table. If the
option is enabled, Fast Data Masker tries to use a masked value. Otherwise, it uses the current value. If the column is not found, it assumes that the value is a literal string value.
Parameters
• Parm1
Specifies the value or column name.
• Parm2
Specifies the value or column name.
• Parm3
Specifies the value or column name.
• Parm4
Specifies the value or column name.
Applies to:
Character
Example:
The values in the
FIRST_NAME
and
LAST_NAME
columns are concatenated in the
FULL_NAME
column (as last name, comma, space, first name). For example,
John
is the value in the
FIRST_NAME
column and
Miller
is the value in the
LAST_NAME
column. The Parm1 value is the
LAST_NAME
column, Parm2 value is a comma (
,
), Parm2 value is a space ( ), and Parm4 value is the
FIRST_NAME
column. The
FULL_NAME
column then includes the value as
Miller, John
. The following table shows the usage:
 Table Column Function Parm1 Parm2 Parm3 Parm4 KeepNulls EMPLOYEE FULL_NAME CONCAT LAST_NAME , FIRST_NAME Y
DECRYPT
The DECRYPT function creates a decrypted version of a column based on the key in Parm1.
Parameters
• Parm1
Specifies the encryption key to use.
Applies to:
Character
Example:
e34;;=
could be converted to
ABC
.
DELETE
The DELETE function deletes ALL rows from the table that contains the column on which the function runs. Use an SQL WHERE clause in Parm1 to specify what data to remove.
If you do not specify a WHERE clause in Parm1, this function removes ALL data from the table that contains the target column!
Parameters
• (Optional) Parm1
Specifies the WHERE clause for the delete.
Applies to:
Date, Character, and Numeric
Examples:
 Table Column Function Parm1 Parm2 Parm3 Parm4 Result ORDERS CURRENT DELETE All entries in all rows in the table ORDERS are deleted. ORDERS CURRENT DELETE WHERE COST > 1000 In the table ORDERS, all rows in which the value of column COST is more than 1000 are deleted.
Note on use of DELETE function from TDM Portal
If you assign this function to a column from the Configure Data Masking page of the Portal, and you wish to delete only selected rows, you must use the WHERE clause in Parm1 to specify which rows to delete. Use of the WHERE clause offered by TDM Portal results in deletion of the entire table.
DOB
The DOB function adjusts the date by adding or subtracting the specified number of days from the original value without changing the age.
Parameters
• Parm1
Specifies the number of days that you want to add or subtract.
• parm2
(Optional) Specifies the date format used by the column being masked. This parameter is relevant when the column is of type varchar or char.
For example: yyyy-MM-dd
Applies to:
Character and Date
Example:
The age
52
(DOB
9/5/1958
) remains
52
10
days to
19/5/1958
. The following table shows the usage:
 Table Column Function Parm1 Parm2 Parm3 Parm4 KeepNulls PERSONS DATE_OF_BIRTH DOB 10 yyyy-MM-dd
DOD
The DOD function adjusts the date by adding or subtracting the specified number of days from the original value without changing the years since death.
Parameters
• Parm1
Specifies the number of days that you want to add or subtract.
• parm2
(Optional) Specifies the date format used by the column being masked. This parameter is relevant when the column is of type varchar or char.
For example: yyyy-MM-dd
Applies to:
Character and Date
Example:
The date of death
10/9/2009
remains at
1
10
days to it; for example,
17/9/2009
. The following table shows the usage:
 Table Column Function Parm1 Parm2 Parm3 Parm4 PERSONS DATE_OF_DEATH DOD 10 yyyy-MM-dd
EMAIL
The EMAIL function masks the column with an auto-generated email ID.
Parameters:
None
Applies to:
Character
Example:
The
EMAIL
column in the table
PERSONS
is masked with an auto-generated email ID; for example, . The following table shows the usage:
 Table Column Function Parm1 Parm2 Parm3 Parm4 KeepNulls PERSONS EMAIL EMAIL
ENCRYPT
The ENCRYPT function creates an encrypted version of a column based on the key in Parm1.
Parameters
• Parm1
Specifies the encryption key to use.
Applies to:
Character
Example:
ABC
could be converted to
e34;,=
Note:
The encrypted version is longer than the original value. So ensure that the column width can accommodate the new value.
ENCRYPTJAVELIN
The ENCRYPTJAVELIN function takes plain text, or text encrypted with the TDM EncryptionUtil mechanism, and returns text encrypted with Javelin's encryption mechanism.
Parameters
• Parm1
The string to encrypt with Javelin's encryption mechanism.
• if Parm2 =
FALSE
:
The string should be
plain text
.
Example:
Result:
^_^ZVWelft+/dnJx4TtAtBdWw==^_^
• if Parm2 =
TRUE
:
The string should be
encrypted
with EncryptionUtil.
If you enter the whole
{cry}
prefix, it is ignored.
Examples:
• Example A:
{cry}R7fScNVu2SGsZtltywB7MKG8SUJDujzq6FxB3QKkGlAOoLQS6f5Q9L3gUxtmE1jQ
Result A:
^_^ZVWelft+/dnJx4TtAtBdWw==^_^
• Example B:
R7fScNVu2SGsZtltywB7MKG8SUJDujzq6FxB3QKkGlAOoLQS6f5Q9L3gUxtmE1jQ
Result B:
^_^ZVWelft+/dnJx4TtAtBdWw==^_^
• (Optional) Parm2
Boolean. Indicates whether to parse Parm1 as plain or encrypted text. Default:
FALSE.
ENCRYPTUSSSN
The ENCRYPTUSSSN function consistently encrypts a nine-digit US Social Security Number. This function only encrypts the last seven digits of the number.
Parameters
• None
Applies to:
Character, Numeric
Example:
789216362
to
783898720
FILL
The FILL function fills a column with a string or character defined in Parm1, covering the entire width of the column.
Parameters
• Parm1
Specifies the fill value to use.
Applies to:
Character
Example:
All characters in the column
ACCT_NUMBER
in the table
ACCOUNT
are filled with the value provided in Parm1. If
Abc
is provided as the fill value, all values in
ACCT_NUMBER
are replaced as
AbcAbcAbcAbc
, covering the complete column width. The following table shows the usage:
 Table Column Function Parm1 Parm2 Parm3 Parm4 ACCOUNT ACCT_NUMBER FILL Abc
Note:
C (Character) or N (Numeric) in Parm1 fills relevant values.
FIXED
The FIXED function masks the column values with the fixed values provided in Parm1.
Parameters
• Parm1
Specifies the fixed value to use.
Applies to:
Date, Character, and Numeric
Example:
The column
ACCOUNT_NUMBER
is masked with the fixed value
11100022
and the column
PERSON_TYPE_CODE
CUST
.
Note:
If you want to set the value to NULL, enter the string
<NULL>
. If you want to maintain a space, enter the string
<SPACE>
. The following table shows the usage:
 Table Column Function Parm1 Parm2 Parm3 PAYMENT_OPTIONS ACCOUNT_NUMBER FIXED 11100022 PAYMENT_OPTIONS PERSON_TYPE_CODE FIXED CUST
FIXEDDAY
The FIXEDDAY function fixes the day part of the date to the value specified in Parm1.
Parameters
• Parm1
Specifies the day value to use.
Applies to:
Date
Example:
The day part of the date in the
ORDER_DATE
column in the table
ORDERS
is fixed with the value
22
. If the date is
1994-01-08
, it becomes
1994-01-22
. The following table shows the usage:
 Table Column Function Parm1 Parm2 Parm3 ORDERS ORDER_DATE FIXEDDAY 22
FIXEDUNIQUE
The FIXEDUNIQUE function adds a sequence number to the value in Parm1 to give unique values for all rows.
Parameters
• Parm1
Specifies the fixed value to which to add the sequence number.
Applies to:
Numeric and Character
Example:
Parm1 is the fixed part of what is generated (so
999
for numeric or
ABC
for character, for example). A sequence is then added to this to make it unique, up to the column width. Therefore, the masked values might be
99910000
or
ABC1000
,
99910001
or
ABC1001
. The following table shows the usage:
 Table Column Function Parm1 Parm2 Parm3 Parm4 MEMBERS CARD_NUMBER FIXEDUNIQUE 999 MEMBERS MEMBERSHIP_NO FIXEDUNIQUE ABC
FORMATDECRYPT1
Decrypts a string encrypted using the FORMATENCRYPT1 masking function and the respective parameters used during encryption. The values of Parm1 to Parm4 must be the same as the values that were used for encryption with FORMATENCRYPT1.
Parameters
• Parm1
(Optional) Specifies the number of start characters to ignore.
• Parm2
(Optional) Specifies the number of end characters to ignore.
• Parm3
(Optional) (If Parm1 and Parm2 are not set) Specifies the number of start characters to mask.
• Parm4
(Optional) (If Parm1 and Parm2 are not set) Specifies the number of end characters to mask.
• Parm5:
Master Key to apply to the decryption algorithm. The value of the master key must be the same as the value that was provided for encryption with FORMATENCRYPT1. This parameter is mandatory.
Limitation:
Currently, FORMATDECRYPT1 does not support decrypting strings that were encrypted through FORMATENCRYPT1 with values for ignored and excluded characters (param6 and param7 of FORMATENCRYPT1).
FORMATENCRYPT
FORMATENCRYPT consistently masks the given column values with the original format. The function produces unique values as long as the original values are also unique, which makes it ideal for masking key columns.
Parameters
None of the following parameters are mandatory:
• PARM1
(Optional) Specifies the number of start characters to ignore.
• PARM2
(Optional) Specifies the number of end characters to ignore.
• PARM3
(Optional) (If PARM1 and PARM2 are not set) Specifies the number of start characters to mask.
• PARM4
(Optional) (If PARM1 and PARM2 are not set) Specifies the number of end characters to mask.
• PARM5
This parameter isn't applicable. If the parameter is provided, it is ignored.
• PARM6
(Optional) Specifies Ignored Chars, that means, which characters of the original input string will not be masked.
Example:
Original input: ABCDE
Masked output with Ignored chars 'BD': L
B
X
D
V.
• PARM7
(Optional) Specifies "Excluded Chars", that means, which characters will not be present in the output string. As the excluded characters will be not present in the output map of characters, the masking algorithm will produce a completely different result from those where they are not defined.
Example:
Original input: ABCDE
Masked output with Excluded chars 'IU': LJAVX
Job parameter:
To use the EXTENDED character map, set the following parameter in the JCL:
`FORMATENCRYPTEXTENDEDCHARS=Y`
Applies to:
Characters and Numbers.
Review the following considerations:
• For numeric columns, FORMATENCRYPT ignores the first digit of input values. This is to avoid the generation of a masked value with leading zeroes, which databases typically truncate, and which can then become identical to another value.
This rule does not apply to character columns, because databases do not truncate character values.
• This function does not mask the first occurrence of a lowercase character. It retains that letter
as is
. For example, aBCd to
aWKj
or
BaB
to
WaJ
. To address this issue, ensure that you enter a lowercase key for the LOWERCASEKEY masking option. For example,
htjugtvffc
a
.
The Masterkey, Ignored and Excluded chars can be set individually for each Column or Field. This means, that for each column in a table, a different Masterkey can be set, so results can vary upon need. When the Excluded characters feature is used, the masking process uses more CPU, because the output map needs to be redone for each given column referred to in the MAPCSV file.
Extended Characters Map:
 À Á Â Ã Ä Å Æ Ç È É Ê Ë Ì Í Î Ï Ð Ñ Ò Ó Ô Õ Ö Ø Ù Ú Û Ü Ý Þ ß à á â ã ä å æ ç è é ê ë ì í î ï ð ñ ò ó ô õ ö ø ù ú û ü ý þ ÿ
FORMATENCRYPT1
Consistently masks the given column values with the original format. The function produces unique values as long as the original values are also unique, which makes it ideal for masking key columns.
This function works pretty much as FORMATENCRYPT, but it has an extended set of 20 group keys against the unique set of keys from FORMATENCRYPT. Each masking group key is split in upper, lower, and numeric keys in a random sequence of characters, giving in total 60 character keys to be used in the masking process. This function also allows the users to set a particular Masterkey to be mixed with those 20 standard keys in other to obtain customized masking results. It also has the ability to work with an EXTENDED character map, listed at the end of this description.
Parameters
None of the following parameters is mandatory:
• (Optional)
Parm1
Specifies the number of starting characters to be ignored.
• (Optional)
Parm2
Specifies the number of ending characters to be ignored.
• (Optional)
Parm3
(If PARM1 and PARM2 are not set) Specifies the starting position of the input string to be masked.
• (Optional)
Parm4
(If PARM1 and PARM2 are not set) Specifies the ending position of the input string to be masked.
• (Optional)
Parm5
A
Master Key
to apply to the encryption algorithm. Defines a user-defined Masterkey to be mixed with the set of the standard 20 masking group keys that are embedded in the product, generating a customized set of 20 masking group keys. If the Masterkey is not defined, the standard set of 20 masking group keys will be used.
This value must meet the following criteria:
• Must be between 1 and 20 characters long. 20 characters is the recommended length for this value. Any characters past the 20th character are ignored, and do not affect the encryption algorithm.
• Must contain ONLY the following characters, in any combination:
• Numbers (between 0 and 9)
• Lower case letters (a to z)
• Upper case letters (A to Z)
• Examples of
valid
Master Key values:
abcd
,
SAKdsjaSFQ
,
32454GFDss
,
• Examples of
invalid
Master Key values:
"!sa£_bcd"
,
'o\$VF11'
,
S?F.Q
,
FDss*.+
,
• PARM6
(Optional) Defines the Ignored Chars, that means, which characters of the original input string will not be masked.
Example:
Original input: ABCDE
Masked output with Ignored chars 'BD': L
B
X
D
V.
• (Optional) PARM7
Specifies Excluded Chars, that means, which characters will not be present in the output string. As the excluded characters will be not present in the output map of characters, the masking algorithm will produce a completely different result from those where they are not defined.
Example:
Original input: ABCDE
Masked output with Excluded chars = 'IU': LJAVX
Job parameter:
To use the EXTENDED characters map, set the following parameter in the JCL:
`FORMATENCRYPTEXTENDEDCHARS=Y`
Applies to:
Characters and Numbers.
The Masterkey, Ignored and Excluded chars can be defined individually for each Column or Field. This means, that you can set a different Masterkey for each column in a table, so results can vary upon need. Because of this, when the Excluded characters feature is used, the masking process uses more CPU because the output map needs to be redone for each given column referred to in the MAPCSV file.
Extended Characters Map:
 À Á Â Ã Ä Å Æ Ç È É Ê Ë Ì Í Î Ï Ð Ñ Ò Ó Ô Õ Ö Ø Ù Ú Û Ü Ý Þ ß à á â ã ä å æ ç è é ê ë ì í î ï ð ñ ò ó ô õ ö ø ù ú û ü ý þ ÿ
FORMATFPE1
Consistently masks the given values while preserving the original length and alphabet. Unlike FORMATENCRYPT and FORMATENCRYPT1 functions, this masking function does not preserve character groups (in particular lower case letters, upper case letters, and digits).
The function produces unique values as long as the original values are also unique, which makes it ideal for masking unique columns or fields. This function implements (and is compatible with) FF1 algorithm - the format-preserving algorithm approved by NIST.
This function doesn't have an associated decrypt functionality.
Parameters
• (Optional)
Parm1
Specifies that the original value should be used to further improve the randomness of the masked value - in this case, uniqueness cannot be guaranteed and that's why is not usable for masking unique columns or fields.
• (Optional)
Parm2
Specifies that the given (character) value should be treated as a number - the leading and trailing spaces will be preserved, together with the leading zeroes and the first digit. The value of this parameter applies only when the alphabet contains digits and nothing else.
• Parm3
Specifies the encryption key to be used for underlying AES encryption as a hexadecimal string with a length of 32, 48, or 64 hexadecimal characters (so the length of AES encryption key will be 128, 192, or 256 bits respectively).
• (Optional)
Parm4
Specifies the delimiters as a space-separated list of single-character delimiters - it uses the same format as the FORMATENCRYPTDELIMITER option. The given value is split first by the provided delimiters and each part is then masked separately. If the delimiters are not specified then no delimiters are used.
• (Optional)
Parm5
Specifies the input and output alphabet as a space-separated list of keywords (DIGITS, LCENG, UCENG, EXTLC, EXTLCFR, EXTUC, EXTUCFR, CHRSPACE, SPECIAL, SPECIAL2). If the data to be masked contains a character that does not match with the alphabet then the masking is aborted. If the alphabet is not specified then the following alphabet is used: DIGITS LCENG UCENG EXTLC EXTUC SPECIAL CHRSPACE.
Applies to:
Characters and Numbers.
The encryption key can be defined individually for each column or field. You can set a different encryption key for each column in a table or each field in a file, so the results can vary upon the requirement.
Alphabet Keywords:
• DIGITS = 0123456789
• LCENG = abcdefghijklmnopqrstuvwxyz
• UCENG = ABCDEFGHIJKLMNOPQRSTUVWXYZ
• EXTLC = ÿabcdefghijklmnopqrstuvwxyzàáâãäåæçèéêëìíîïðñòóôõöøùúûüýþ
• EXTLCFR = àâäçéèêëîïôöùûü
• EXTUC = ßABCDEFGHIJKLMNOPQRSTUVWXYZÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖØÙÚÛÜÝÞ
• EXTUCFR = ÀÂÄÇÉÈÊËÎÏÔÖÙÛÜ
• CHRSPACE = (space)
Example:
The encryption key to be used for masking is chosen to be 11223344556677889900AABBCCDDEEFF.
Assuming that the values stored in the CARD_NUMBER column have the following format 0043321-456 765128324, the delimiter field can accordingly be set to <- SPACE> and you can use DIGITS in the accepted characters field.
The input data is first split by spaces and hyphens, and each part is masked separately.
Each masked part includes digits only and the leading zeros (together with the first non-zero digit) are preserved (masked as the number set to Y).
Input data:
0043321-456 765128324
0040035-469 771001099
The following table shows the usage:
 Table Column Function Parm1 Parm2 Parm3 Parm4 Parm5 EMPLOYEE CARD_NUMBER FORMATFPE1 N Y 11223344556677889900AABBCCDDEEFF - SPACE DIGITS
FORMATHASH
The FORMATHASH function hashes lowercase letters to lowercase letters, uppercase letters to uppercase letters, and digits to digits. All other characters remain the same. The function ensures the same format and consistent masks for the same original value but does not ensure uniqueness.
Parameters:
None
Applies to:
Character
Example:
The values in the
PART_ID
column are masked by using the FORMATHASH function; for example,
ABC/123-dur~678
becomes
VRT/529-cas~210
after masking. The following table shows the usage:
 Table Column Function Parm1 Parm2 Parm3 PARTS PART_ID FORMATHASH
FORMATLUHN
The FORMATLUHN function consistently changes the current value, preserving the format (letters to letters, digits to digits). The digits are used to calculate the check digit, which then replaces the last digit in the resulting masked value. The function produces unique values as long as the original values are also unique.
Parameters:
• Starting From Position
(Optional) Defines the initial character position where to start masking, counting from 1. By default it masks all characters up to the last. Can be used together with
.
• Number of Digits to Mask
(Optional) Defines the number of digits to mask. By default it starts at the first character. Can be used together with
Starting From Position
.
• Number of Last Digits to Mask
(Optional) Defines the number of digits to mask, counting backwards from the end. The last digit will be the checksum and does not count. If
Start From Position
or
are defined,
Number of Last Digits to Mask
is ignored.
Applies to:
Number and Character
Example:
The values in the
PART_NUMBER
column are masked by using the FORMATLUHN function; for example,
ABC/123-A1
to
VJI/802-E9
,
DEF/456-B1
to
YML/135-F4
,
GHI/123-C3
to
BPO/802-G9
. The following table shows the usage:
 Table Column Function Parm1 Parm2 Parm3 PARTS PART_NUMBER FORMATLUHN
Example:
I want to format "1234567890" starting from position "3". The function returns "1297432113", where "12" remains unchanged, "3456789" is encrypted to "9743211", and the checksum is calculated as 3 and replaces the last digit.  The following table shows the usage:
 Table Column Function Parm1 Parm2 Parm3 STAFF ID_NUMBER FORMATLUHN 3
Example:
I want to format "2" digits of "1234567890". The function returns "6834567894", where "3456789" remains unchanged, the first 2 digits "12" are encrypted to 68, and the checksum is calculated as 4 and replaces the last digit. The following table shows the usage:
 Table Column Function Parm1 Parm2 Parm3 STAFF ID_NUMBER FORMATLUHN 2
Example:
I want to format the last "2" digits of "1234567890". The function returns "1234567431", where "1234567" remains unchanged, "89" is encrypted to "43", and the checksum is calculated as 1 and replaces the last digit. The following table shows the usage:
 Table Column Function Parm1 Parm2 Parm3 STAFF ID_NUMBER FORMATLUHN 2
The FORMATMASK function masks a character value, retaining the original format. Only characters A through Z, characters a through z, and digits 0 through 9 are masked.
Parameters
• Parm1
Specifies the mask key; for example, 345.
Applies to:
Character and Numeric
Example:
Hash key
345
produces a masked output; for example,
Aa999
becomes
sK110
. The following table shows the usage:
 Table Column Function Parm1 Parm2 PURCHASE ORDER_NUMBER FORMATMASK 345
FORMATVIN
This function validates whether the input is a valid Vehicle Identification Number.
• If the input does not have 17 characters, the function leaves it as is.
• If the input has 17 characters and is a valid VIN, the function leaves it as is.
• If the input has 17 characters but is not a valid VIN, it changes the check digit to make it valid.
Parameters:
None
Applies to:
Characters
Example:
The values in the
VEH_ID
column are masked by using FORMATVIN; for example, the valid
JT5RA65K
2
F4054074
stays
JT5RA65K
2
F4054074
, and JT5RA65K
2
F4054070 becomes JT5RA65K
5
F4054070 (invalid, check digit corrected).
The following table shows the usage:
 Table Column Function Parm1 Parm2 Parm3 VEH VEH_ID FORMATVIN
GENCARD
The GENCARD function masks the column values with the 15- and 16-digit credit card numbers, keeping the original format.
Parameters:
None
Applies to:
Character and Numeric
Example:
The
CARD_NUMBER
5187230394132622
,
376152764010075
,
4611647914049615
. The following table shows the usage:
 Table Column Function Parm1 Parm2 Parm3 Parm4 CREDIT_CARDS CARD_NUMBER GENCARD
GUID
The GUID function generates a globally unique identifier, which is a 36-character value (including hyphens).
Parameters:
• Parm1
If this parameter contains the character string 'COLLAPSE', the unique identifier is without hyphens. This parameter is case-sensitive.
Applies to:
Character
Example:
The
SECTION_ID
column masks the existing value as
94b82ed6-e941-4185-b84c-53cc0f56a006
. The following table shows the usage:
 Table Column Function Parm1 Parm2 Parm3 Parm4 Result STUDENT SECTION_ID GUID 94b82ed6-e941-4185-b84c-53cc0f56a006 STUDENT SECTION_ID GUID COLLAPSE 94b82ed6e9414185b84c53cc0f56a006 STUDENT SECTION_ID GUID Collapse 94b82ed6-e941-4185-b84c-53cc0f56a006
HASH
The HASH function returns HASH values for the integer fields.
Parameters
• Parm1
Specifies the seed value for hash.
• Parm2 (Optional)
Specifies the maximum number of digits allowed.
Applies to:
Number
Example:
The
NUMBERS
column in the table
TEST1
is hashed with the seed value of
35
and
5
as the maximum number of digits allowed. The following table shows the usage:
 Table Column Function Parm1 Parm2 Parm3 Parm4 TEST1 NUMBERS HASH 35 5
The following are the original and masked values for this example:
• 101
to
10312
• 102
to
73780
• 103
to
88091
HASHABN
The HASHABN function hashes an Australian Business Number with valid check digits.
Parameters:
None
Applies to:
Character
Example:
The value
51824753556
in the
VALID_ABN
19503876902
. The following table shows the usage:
 Table Column Function Parm1 Parm2 PERSON VALID_ABN HASHABN
HASHACN
The HASHACN function hashes an Australian Company Number with valid check digits.
Applies to:
Character
Parameters:
None
Example:
The values
786362338
,
784862335
, and
789123468
in the
VALID_ACN
465485787
,
463985784
, and
468246817
respectively. The following table shows the usage:
 Table Column Function Parm1 PERSON VALID_ACN HASHACN
HASHCARD
The HASHCARD function retains the first two digits of the original credit card number (which define the credit card type) and hashes the remaining digits, retaining the original length of the number. This function also ensures that the last digit is the correct check digit for a credit card number.
Note:
Use this function to mask a number that is part of a key field. If the original number is unique, then it will continue to be unique after the HASHCARD function.
Parameters:
None
Applies to:
Character and Numeric
Example:
The value
5533716111165678
in the
CARD_NUMBER
5599197827275710
. Note that the first two digits remain the same. The following table shows the usage:
 Table Column Function Parm1 EMPLOYEE CARD_NUMBER HASHCARD
HASHCARD1
Hash a credit card number using FORMATENCRYPT1. This function masks digits only. If the data to be masked contains characters and digits, use the start and end position parameters to exclude non-numeric characters from masking. If the input value does not have a valid length for a credit card number, the number will be encrypted and its length will remain invalid. If the input value is null, 0, or 0000000000000000, the value will not be changed.
Applies to:
Character and Numeric
• Parm1 - Card Start Position
(Optional): Position where the credit card number starts in the String. Default value is 1.
Example: ABC1234567890123456. In this example, Card Start Position is 4.
• Parm2 - Card End Position
(Optional): Position where the credit card number ends in the String. Default value is 16.
Example: 1234567890123456ABC. In this example, Card End Position is 17.
• Parm3 - Mask Start Position
(Optional): Position in the credit card number where to start masking. Default value is 1.
Example: 1234567890123456. In this example, Mask Start Position is 4.
• Parm4 - Mask End Position
(Optional): Position in the credit card number where to stop masking. Default value is 16.
Example: 1234567890123456. In this example, Mask End Position is 14.
• Parm5 - Master Key
(Optional): Custom user key that will be mixed with the internal key set as it is defined for FORMATENCRYPT1. For more information, see FORMATENCRYPT1.
• Parm6 - Custom End Digits
(Optional): A sequence of digits in the end of the credit card number that will not be masked. The check digit will not be updated.
Example: 1234567890123000. In this example, Custom End Digits is 00.
The following table shows the usage:
 Table Column Function Parm1 Parm2 Parm3 Parm4 Parm5 Parm6 EMPLOYEE CARD_NUMBER HASHCARD1 4 13 3 6
In this example, we are hashing the alphanumeric string
ABC123456789DEF
. We use Start and End Position parameters to exclude the first 3 characters and the last 3 characters, the numeric substring is now 123456789. Then we use the Start and End Masking Position parameters to mask the substring 3456 only. In the end result, the xxxx positions is replaced by the hashed value and y is replaced by the check digit:
ABC12xxxx78yDEF
. This example does not use the key and custom end digits.
HASHCARD4
The HASHCARD4 function hashes only the last four digits of the original credit card number (which define the credit card type) while retaining the original length of the number. The function does not mask the bad data. Examples of bad data include data containing fewer than thirteen digits or any length of character type data. For example,
1234
,
54365
,
abcdefghijklmnopqrstuvw
,
abcdef
,
teresfdgertygd
.
Parameters:
None
Applies to:
Character and Numeric
Example:
The value
307406777921
1613
(before using the function) in the
CARD_NUMBER
307406777921
1571
(after using the function). Note that only the last four digits are masked. The following table shows the usage:
 Table Column Function Parm1 EMPLOYEE CARD_NUMBER HASHCARD4
HASHDAYS
The HASHDAYS function takes an existing date and consistently changes only the day part of it to a value between 1 and 27. If the date is stored as a string, specify the date format.
Example:
The days of dates in the column HIRE
_DATE
2018-07-24
to
2018-07-01
, and from
1997-12-16
to
1997-12-27
.
 Table Column Function Parm1 EMPLOYEE HIRE_DATE HASHDAYS YYYY-MM-DD
HASHDOB
The HASHDOB function hashes a date, keeping the original age.
Parameters:
None
Applies to:
Date and Character
Example:
Date of birth is masked by hashing an existing date of birth. For example, the date in the column
BIRTH_DATE
is consistently masked (retaining the age)
2016-05-24
to
2016-05-28
,
1999-12-16
to
1999-12-26
. The following table shows the usage:
 Table Column Function Parm1 Parm2 Parm3 EMPLOYEE BIRTH_DATE HASHDOB
HASHFINNISHID
Takes an existing Finnish ID and hashes it to a new value. If the original values are unique, the hashed values are also unique.
Parameters:
None
Applies to:
Character
Examples:
• 010120-029J to 010120-708E
• 010120-001M to 010120-780S
• 010120-002N to 010120-781T
• 010120-003P to 010120-782U
HASHIBAN
Takes an existing IBAN number and consistently changes it to a new IBAN number. The new number has valid IBAN check digits.
Invalid input numbers, for example those not starting with a valid country code, or having an incorrect length, remain unchanged.
Parameters:
None
Applies to:
Character
Example:
GB82WEST12345698765432
HASHLOV
The HASHLOV function hashes the current value to consistently pick a value from a seed list or table in Parm1. For XML files, you can choose which value of an XML tag to hash on, for more information, see Fast Data Masker Best Practices.
Parameters
• Parm1
Specifies the seed list or table.
• Parm2
If using a seed table from a database rather than a file. This is the optional column value from the seed table. So, for example,
3
would return the value for
rd_ref_value3
. If linking columns using seed files, you would use the following naming convention:
,
.
• Parm3 (Optional)
Typically, HASHLOV hashes the value of the column to be masked. If you want the hash to work on a different column, enter that column name here. This would typically be a column with unique values in it to minimize duplication.
• Parm4 (Optional)
Specifies the seed value for hash.
Note:
Set the
MD5HASHLOV
Options
tab to
Y
to use
MD5
hashing with the HASHLOV function. By default, the function uses a Java hash algorithm.
Applies to:
Character, Numeric, and Date
Example:
The current value in the
FIRST_NAME
column in the table
PERSONS
is hashed to consistently pick a value from the seed list
female_english.txt
. The following table shows the usage:
 Table Column Function Parm1 Parm2 Parm3 PERSONS FIRST_NAME HASHLOV female_english.txt
HASHLOV1
The HASHLOV1 function hashes the current value to consistently pick a value from a database-oriented seed list in Parm3.
Parameters
• Parm1
Represents the data category in the seed data table.
• Parm2
Represents the column number in the seed data table that is used for the masking.
• Parm3
Specifies the column value to define the list. For example, if column name=
CITY
and current value is
New York
, then only
New York
• Parm4
Specifies an integer value describing the maximum length of the value to use from Parm3. So, for example, if Parm3 is a postcode with the current value as
OX29 4TP
and you set Parm4 to
4
OX29
.
• (MS SQL only)
FASTSEEDFETCH
Uses an improved algorithm to load seed tables.
Value:
Y or N (default N)
Note:
Override SQL is used to get the hash integer value rather than the column you are masking. This is important when linking columns to choose a column with the most unique values.
Applies to:
Character, Numeric, and Date
Example:
This example consistently masks the column
CITY
in the table
.
It takes the value for the column defined in "Override SQL" (
) and uses this to get a hashed index. The seed list is from the data category
US CITY STATE ZIP COUNTY
and for the
STATE_PROVINCE
values for the current state (so you have different buckets for
CA
,
OH
, and so on).
You use the value from
STATE_PROVINCE
to get the hashed value rather than the column you are masking to get a better spread of data. Otherwise, for example, all the addresses for the state
CA
would have the same value. As in RANDLOV1, the value for
RD_ REF_VALUE
must match that of the reference column (in this case,
STATE_PROVINCE
).
Notes:
• To provide default values, in this case for postal codes that exist in the table to be masked (
) but not in the seed data category (
US CITY STATE ZIP COUNTY
), you would need to add a line in the seed data table for that category with default values (
RD_REF_VALUE 'DEFAULT'
).
• Set the
MD5HASHLOV
Options
tab to
Y
to use
MD5
hashing with the HASHLOV1 function. By default, the function uses a Java hash method.
The following table shows the usage:
 Table Column Function Parm1 Parm2 Parm3 Parm4 KeepNulls DateFormat Cross Reference Override SQL EMPLOYEE_ADDRESS CITY HASHLOV1 US CITY STATE ZIP COUNTY 2 STATE_PROVINCE Y ADDRESS_ID
HASHPASSPORT
The HASHPASSPORT function consistently masks a passport number, keeping the original length.
Parameters:
None
Applies to:
Character and Numeric
Example:
The passport number
359866285
in the
PASSPORT
282777060
. The following tables shows the usage:
 Table Column Function Parm1 Parm2 EMPLOYEE PASSPORT HASHPASSPORT
HASHPHONE4
The HASHPHONE4 function masks the last four digits of a phone number. The remaining digits are not changed. The function does not mask the bad data. Examples of bad data include data containing fewer than four digits or characters of any length. For example,
554
,
298
,
avd
,
abcf
, or
.
Parameters:
None
Applies to:
Character and Numeric
Example:
The phone number
1712441
9639
in the
PHONE
1712441
7572
. Note that only the last four digits are masked. The following table shows the usage:
 Table Column Function Parm1 Parm2 Parm3 Parm4 PEOPLE PHONE HASHPHONE4
HASHRUT
The HASHRUT function takes an existing RUT number (Chilean Social Security number) and hashes the first 8 digits, then adds the appropriate check digit at the end. This is the method that should be used to ensure consistency across tables.
Note:
The string length of a RUT is 9, so HASHRUT only works on string columns.
Parameters:
None
Applies to:
Numeric and Character
Example:
The following table shows the usage:
 Table Column Function Parm1 Parm2 Parm3 Parm4 PEOPLE RUT_NUMBER HASHRUT
HASHSIN
The HASHSIN function masks the given column values with a unique Canadian Social Insurance Number, which is a 9-digit number.
The input string must be a 9-digit numeric value, which can include any number of non-numeric separators. If the value includes non-numeric separators, HASHSIN includes these separators in the output value.
Parameters:
Two – both the parameters are optional. If Parm1 is specified as ‘D’, signaling a default value to be returned, then Parm2 becomes mandatory.
• Parm1
Indicates the action to be taken if the input does not contain 9 digits:
None: only mask 9-digit inputs, return the input as output if not 9
‘F’        mask input using the available number of digits
• Parm2
Contains the value to be substituted if the input does not contain 9 digits.
Applies to:
Numeric (input SIN) and Character (separators)
Example:
The following table shows examples of HASHSIN's operation:
 Input string Output string 123456789 846812368 123-456-789 846-812-368 123##456RR789 846##812RR368 0123456789 0123456789 (no masking, more than 9 numbers) 1234#5678 1234#5678 (no masking, fewer than 9 numbers)
HASHSPANISHID
The HASHSPANISHID function consistently masks Spanish ID numbers (NIF or NIE). You can also use this function to mask Spanish Company IDs (CIF numbers).
Parameters:
• Parm1
If parm1 = Y, the function will also mask CIF numbers.
Applies to:
Character
HASHTIN
The HASHTIN function masks the given column values with a unique US Tax Identification Number, which is a 9-digit number starting with 9.
Parameters:
None
Applies to:
Character and Numeric (having integer values)
Example:
The following values in the
TIN_ID
• 12323232435
to
985861557
• 12323232436
to
944772017
• 12323232437
to
900772597
The following table shows the usage:
 Table Column Function Parm1 Parm2 Parm3 PEOPLE TIN_ID HASHTIN
HASHTURKISHID
The HASHTURKISHID function masks the 11-digit Turkish Identification Number.
Parameters:
None
Applies to:
Numeric and Character
Example:
The value
12345678901
in the column
TURKISH_ID
80257913544
. The following table shows the usage:
 Table Column Function Parm1 Parm2 Parm3 PEOPLE TURKISH_ID HASHTURKISHID
HASHTURKISHTAXID
The HASHTURKISHTAXID function masks an existing 10-digit Turkish Tax Identification Number.
Parameters:
None
Applies to:
Numeric, Character
HASHUSSSN
The HASHUSSSN function consistently hashes a US Social Security Number, retaining the original length of the number.
Parameters:
None
Applies to:
Character and Numeric
Example:
The value
198689580
in the
USSSN_ID
228322064
. The following table shows the usage:
 Table Column Function Parm1 Parm2 Parm3 PEOPLE USSSN_ID HASHUSSSN
HASHUSSSN4
The HASHUSSSN4 function masks a US Social Security Number's last four digits, retaining the remaining numbers and original length.
Parameters:
None
Applies to:
Character and number
Example:
The value
58019
8689
in the
USSSN_ID
58019
1232
. Note that only the last four digits are masked. The following table shows the usage:
 Table Column Function Parm1 Parm2 Parm3 PEOPLE USSSN_ID HASHUSSSN4
IGNORE
The IGNORE function ignores the mask and retains the value if no cross-reference or default value can be found.
Parameters
• Parm1 (Optional)
Specifies the cross reference.
• Parm2 (Optional)
Contains the default value.
Applies to:
Character, Numeric, and Date
Example:
If Parm1 is absent, IGNORE reverts to using the default value set in Parm2. If Parm2 is also absent, IGNORE masks and uses the existing value. The following table shows the usage:
 Table Column Function Parm1 Parm2 Parm3 Parm4 PERSONS FIRST_NAME IGNORE
INTRANGE
The INTRANGE function masks the column with a random value between Parm1 and Parm2.
Parameters
• Parm1
Contains the start value of the integer.
• Parm2
Contains the end value.
Note:
The maximum value for Parm2 is
2147483647
. If the database accepts decimal values, you can also use the NUMERICRANGE masking functions.
Applies to:
Character and Numeric
Example:
The values in the column
NUM_CARDS
are replaced with integer values between
100
and
110
; for example,
20987 to 103
,
34572 to 105
, and so on. The following table shows the usage:
 Table Column Function Parm1 Parm2 Parm3 CARDS NUM_CARDS INTRANGE 100 110
LUHN
The LUHN function generates a number of a given length, with the correct Luhn algorithm check digit at the end.
The Luhn algorithm is designed to detect and prevent accidental errors, and detect valid numbers from random numeric strings. It is not intended as a secure hash.
Parameters
• Parm1
Specifies the length of the number to generate.
Applies to:
Character and Numeric
Example:
The
NUMBER
column uses the LUHN function to mask the existing values with new values of length 5; for example,
103
to
98715
,
110
to
91702
, and so on. The following table shows the usage:
 Table Column Function Parm1 EMPLOYEE NUMBER LUHN 5
The MASKBELGIANID function masks a Belgian National Identification Number based on parameters provided. If the 'Use Masked Values' check box is selected, FDM uses masked values in the current masking routine.
Parameters:
• Parm1 — Date of Birth
Specifies date of birth.
• RANDOM
• 1999-01-02 — A literal date value. Provide a date format.
• DOB_COLUMN — The Name of the column containing the date of birth. Provide a date format.
• Parm2 — Gender
Specifies the gender.
• RANDOM
• M
• F
• GENDER_COLUMN — Name of the column containing the gender values M or F.
Applies To:
Numeric, Character
Example:
 Table Column Function Parm1 Parm2 Parm3 Parm4 PERSONS BELGIAN_ID_COLUMN MASKBELGIANID DOB_COL G_COL
MASTERCARD
The MASTERCARD function generates a random Mastercard credit card number.
Parameters:
None
Applies to:
Character and Numeric
Example:
Generates a valid Mastercard number in the CARD column, such as
5532800794260091
,
5185633632025254
,
5148072171231971
, and so on. The following table shows the usage:
 Table Column Function Parm1 Parm2 PEOPLE CARD MASTERCARD
MOVETOKEN
Moves a token in a string from first to last position, or from last to first position. Tokens are separated by a delimiter of your choice.
Parameters:
• Parm1
Specify Y to move the first token to the last position. Specify N to move the last token to the first position.
• Parm2
Defines the delimiter that separates tokens. Defaults to space. Enter a comma here if the data is in comma-separated format (CSV).
Applies to:
Character
Example:
When the delimiter is the space character, and you move the end token to start, then "Micah, Albrecht Harry" becomes "Harry Micah, Albrecht". If you move the start token to end, "Micah, Albrecht Harry" becomes "Albrecht Harry Micah,".
 Table Column Function Parm1 Parm2 PEOPLE NAME MOVETOKEN N
NEXTVAL
The NEXTVAL function finds the next value from an Oracle sequence. If it is the first time the sequence is used, it starts at 1. The value is then incremented by 1 for each subsequent sequence.
Note:
You must have an Oracle XREF connection set if you use NEXTVAL.
Parameters
• Parm1
Specifies the name of the sequence.
Applies to:
Numeric
Example:
An Oracle sequence "FirstSequence" is called and used to update 20,000 fields in one run. When the sequence is called next time, the run starts from 20,001.
NINO
The NINO function generates a random UK National Insurance Number.
Parameters
• Parm1 (Optional)
Specifies the separator character.
Applies to:
Character
Example:
The NUMBER_UK column uses the NINO function to generate a value such as
NB-00-67-21-B
if using (-) as the separator character. The following table shows the usage:
 Table Column Function Parm1 Parm2 PEOPLE NUMBER_UK NINO
NUMERICRANGE
The NUMERICRANGE function masks the column with numeric values between Parm1 and Parm2. Use this function to generate values with a decimal, unlike range, which uses whole numbers.
Parameters
• Parm1
Specifies the start value of the range.
• Parm2
Specifies the end value of the range.
Applies to:
Numeric
Example:
The column
ORDER_TOTAL
is replaced with values between
40.01
and
49.99
. The following table shows the usage:
 Table Column Function Parm1 Parm2 Parm3 Parm4 KeepNulls ORDERS ORDER_TOTAL NUMERICRANGE 40.01 49.99 N
NUMHASH
The NUMHASH function hashes a numeric value in a character column as digits.
Parameters:
Parm1, Parm2 (Optional), Parm3 (Optional)
Note:
Parm2 is the maximum length of the data, Parm3 is the minimum length.
Applies to:
Character
Example:
Numeric value in a character column is hashed by the seed value to create a numeric string of length between the values in Parm2 and Parm3.
OR
The OR function, in conjunction with WHERE, lets you restrict your mask to certain rows. For example, you can use separate masking rules from credit cards/direct debit expiry dates to invoices based on the PAYMENT_TYPE_CODE column.
Notes
• The ADD function must be used on a separate row to the WHERE clause.
• The OR function is for masking flat files only, as there is no SQL file.
Parameters
• Parm1
Specifies the SQL WHERE clause.
Applies to:
Character, Numeric, and Date
Example:
The following table shows the usage:
 Table Column Function Parm1 Parm2 PERSONS WHERE PAYMENT_TYPE_CODE LIKE 'CC' PERSONS OR PAYMENT_TYPE_CODE LIKE 'DD' PERSONS EXPIRE_DATE ADDRANDOMDAYS -7 21
The PARTMASK function masks the existing value, replacing only alphabets (if Parm1 is set to "C") or only numerics (if Parm1 is set to "N"). The case of alphabets is retained.
Note:
Individual characters are replaced by a randomly selected character.
Parameters
• Parm1
Specifies whether to replace alphabets (C) or digits (N).
Applies to:
Character
Example:
POSTCODE
column of the
table. For example, the postcode
OX29 4TP
becomes
OX34 8TP
. The following table shows the usage:
PHONE_01
The PHONE_01 function replaces digits 0 through 9 with digits in Parm1 or fixed replacement value.
Parameters
• Parm1 (Optional)
Specifies the digits to use for replacement.
Applies to:
Character and Numeric
Example:
Each value in the number
0123456789
is masked with a corresponding value from the number
6721843283
, so 1 = 6, 2 = 7, 3 = 2, and so on. The following table shows the usage:
 Table Column Function Parm1 Parm2 Parm3 Parm4 PERSONS PHONE_NUMBER PHONE_01 6721843283
The POSITIONMASK function masks a value based on positional rules, that you define in Parm1. Separate each rule with a hyphen (no spaces).
Parameters:
• Parm1
Specifies the rules for each position in the output value, from the following formula:
• RD
nnn
L: Rando
nnn
m digit at position
nnn
from left.
• RD
nnn
R: Random digit at position
nnn
from right.
• RA
nnn
L: Random alphabetic character at position
nnn
from left.
• RA
nnn
R: Random alphabetic character at position
nnn
from right.
• RC
nnn
L: Random alphanumeric character at position
nnn
from left.
• RC
nnn
R: Random alphanumeric character at position
nnn
from right.
• F#
nnn
L: Fixed digit (
#
) at position
nnn
from left.
• F#
nnn
R: Fixed digit (
#
) at position
nnn
from right.
• Fa
nnn
L: Fixed alphabetic character (
a
) at position
nnn
from left.
• F
a
nnn
R: Fixed alphabetic character (
a
) at position
nnn
from right.
Notes
• For any position for which you do not provide a rule, the original value remains as the output value.
• If the old value is null or all blanks, the function skips the row.
Applies to:
Character
Example:
The function masks the first three characters of each value of the
PHONE_NUMBER
column in the table
PEOPLE
, with the fixed value
9.
The rest of the digits remain as their original values. The following table shows the usage:
 Table Column Function Parm1 PEOPLE PHONE_NUMBER POSITIONMARK F9001L-F9002L-F9003L
Therefore, the resultant masked value is
999XXXXXX
, where
X
is the existing value.
For example,
1235553283
becomes
9995553283
, and
9238974398
becomes
9998974398
.
RANDEIN
The RANDEIN function masks an existing value with a randomly generated US Employer ID Number.
Parameters:
• Parm1
Specifies the separator character.
Applies to:
Character
Example:
The following table shows the usage:
 Table Column Function Parm1 Parm2 Parm3 COMPANY EMPLOYER_NUMBER RANDEIN -
RANDHIC
The RANDHIC function masks an existing value with a randomly generated US Health Insurance Claim Number.
Parameter:
• Parm1
Specifies the separator character.
Applies to:
Character
Example:
The following table shows the usage:
 Table Column Function Parm1 Parm2 EMPLOYEE NUMBER RANDHIC -
RANDLOV
The RANDLOV function masks the column values with randomly selected values from the seed file.
Note:
The data for RANDLOV function can also be drawn from database tables. Select the columns you want to use and place them in order in the mapping file (for example,
RD_REF_VALUE2
,
RD_REF_VALUE3
). Note that columns drawn from the database tables do not contain a .txt suffix.
Parameters
• Parm1
Specifies the seed file name.
Applies to:
Character, Numeric, and Date
Example:
The following table shows the usage:
 Table Column Function Parm1 Parm2 Parm3 Parm4 ADDRESS CITY RANDLOV uktowns.txt ADDRESS STATE_PROVINCE RANDLOV ukpostcode3.txt PERSONS LAST_NAME RANDLOV lastnameindian.txt
RANDLOV1
The RANDLOV1 function generates random addresses, cities, states/provinces, and so on that are valid for the value specified in Parm3 from a seed table.
Parameters
• Parm1
Specifies the seed file name.
• Parm2
Specifies the position of the column in gtsrc_reference_data.
• Parm3
Specifies the column in the table from where to get the seed data.
• Parm4 (Optional)
Specifies the maximum length to test for Parm3. For example, if the postcode is
OX29 4TP
and Parm4 is set as
4
, this function looks only for
OX29
.
Applies to:
Character, Numeric, and Date
Example:
In the example table, PARM3 is the column
POSTAL_CODE
. This column is used to reference the
rd_ref
values
3
,
5
, and
4
from the
gtrsc_reference
data seed table. The reference is done by using
rd_ref_id
of
and the postal code stored in
rd_ref
value.
Note:
To provide default values, in this case for postal codes that exist in the table to be masked (
) but not in the seed data category (
), add a line to the seed data table for that category with default values (
RD_REF_VALUE 'DEFAULT'
).
The following table shows the usage:
RANDOM
The RANDOM function masks the column with random values between Parm1 and Parm2.
Parameters
• Parm1
Specifies the start value of the range.
• Parm2
Specifies the end value of the range.
Note:
Provide Parm1 and Parm2 in the format YYYYMMDD for dates.
Applies to:
Character, Numeric, and Date
Example:
The value in the
ORDER_DATE
column is replaced with a random date between 2001-Sept-18 to 2002-Nov-15. The following table shows the usage:
 Table Column Function Parm1 Parm2 Parm3 Parm4 KeepNulls ORDERS ORDER_DATE RANDOM 20010918 20021115 N
RANDOMBLOB
The RANDOMBLOB function randomly takes BLOB data from a file in the BLOBS sub-directory and loads into this BLOB column. This must contain files of type of data you want to mask for example .pdf or .gif.
Parameters:
None
Applies to:
Character
Example:
The following table shows the usage:
 Table Column Function Parm1 Parm2 ORDERS ORDER_TYPE RANDOMBLOB
RANDOMDATE
The RANDOMDATE function replaces an existing date value with a random value between Parm1 and Parm2.
Note:
You cannot use RANDOMDATE on a DATE type column.
Parameters
• Parm1
Specifies the minimum date value.
• Parm2
Specifies the maximum date value.
Applies to:
Character
Example:
The date values in the
ORDER_DATE
column are replaced with a random date between 18-Sept-2001 and 15-Nov-2002. The following table shows the usage:
 Table Column Function Parm1 Parm2 Parm3 Parm4 KeepNulls ORDERS ORDER_DATE RANDOMDATE 20010918 20021115 N
RANDOMDAYS
The RANDOMDAYS function changes the day part of a date to a random value.
Parameters:
None
Applies to:
Date and Character
Example:
The day value in the
ORDER_DATE
column is masked to a random value (for example,
2001-01-22
to
2001-01-18
). The following table shows the usage:
 Table Column Function Parm1 Parm2 ORDERS ORDER_DATE RANDOMDAYS
RANDOMTXT
The RANDOMTXT function replaces the column with random text.
Parameters
• Parm1
Specifies the minimum length of the text.
• Parm2
Specifies the maximum length of the text.
• Parm3
Specifies that entering U returns all uppercase letters and L returns all lowercase letters.
Applies to:
Character
Example:
The
FIRST_NAME
column has the value
XrzFF
. The length of the text string is between
3
and
12
; the case is set to uppercase. In this case, the value changes to
OELQ
. The following table shows the usage:
 Table Column Function Parm1 Parm2 Parm3 Parm4 KeepNulls PERSONS FIRST_NAME RANDOMTXT 3 12 U N
RANDSSN
The RANDSSN function masks the column with a randomly generated US Social Security Number.
Parameters
• Parm1 (Optional)
Acts a separator for the SSN.
Applies to:
Character and Numeric
Example:
The value in the column
ID
is replaced with a random US Social Security Number. Entering a separator character into Parm1 (that is, *), as in the example below, generates a social security number like
987*65*4320
. The following table shows the usage:
 Table Column Function Parm1 Parm2 Parm3 Parm4 KeepNulls PERSONS ID RANDSSN * N
REFLOV
The REFLOV function uses the numeric value from the specified column to consistently pick a value from a seed list or table in Parm1.
Parameters
• Parm1
Specifies the seed list or table.
• Parm2
If using a seed table from a database rather than a file. This is the optional column value from the seed table. So, for example, 3 would return the value for rd_ref_value3. If linking columns using seed files, you would use the following naming convention: address.1.txt, address.2.txt.
• Parm3
The numeric column used for getting value used to get the seed value. If the numeric value is larger than the seed list then integer division is used so that the resulting value is within the seed list size. The masking option USEMASKEDVALUES can be enabled to use the masked value from the numeric column.
Applies to:
Character, Numeric, and Date
Example:
The numeric value in the NAME_INDEX column in the table PERSONS is used to consistently pick a value from the seed list female_english for the column FIRST_NAME at the same table. The following table shows the usage:
 Table Column Function Parm1 Parm2 Parm3 PERSONS FIRST_NAME REFLOV female_english NAME_INDEX
REGEXPREPLACE
The REGEXPREPLACE function searches the column values for the regular expression mentioned in Parm1 and replaces it with the character pattern mentioned in Parm2. The REGEXPREPLACE operation is case-sensitive.
Parameters
• Parm1
Specifies the regular expression you want to search for in the column.
• Parm2
Specifies the character pattern you want to use for replacing the expression.
Applies to:
Character and numeric
Example:
All the first names that match the regular expression
Fir.*
are replaced with a value
John
in the column
FIRST_NAME
. The regular expression
Fir.*
searches for all the names in the
FIRST_NAME
column where the first three characters of the names are
Fir
. For all such first names, the value
John
is used. The following table shows the usage:
 Table Column Function Parm1 Parm2 Parm3 Parm4 KeepNulls PERSONS FIRST_NAME REGEXREPLACE Fir.* John Y
Note:
In the case of a flat file, the Table column remains empty.
REGEXPSUBSTR
The REGEXPSUBSTR function returns a sub-string that matches the specified regular expression.
Parameters
• Parm1
Specifies the regular expression that you want to search for in the column.
• Parm2
Specifies the start position.
• Parm3
• Parm4
Applies to:
Character
Example:
The regular expression
[a-j].*
(with the start position as
1
) searches all the names in the
FIRST_NAME
column where the first character includes a letter from
a
through j. The function then extracts the sub-strings from all the names that match the given pattern. For example, if the
FIRST_NAME
column includes a string
Jhonson
, the REGEXPSUBSTR function in this case extracts the sub-string as
honson
. The following table shows the usage:
 Table Column Function Parm1 Parm2 Parm3 Parm4 KeepNulls PERSONS FIRST_NAME REGEXSUBSTR [a-j].* 1 Y
Note:
In the case of a flat file, the Table column remains empty.
REPLACE
The REPLACE function searches the column values for the character pattern specified in Parm1 and replaces it with the character pattern specified in Parm2. The replace operation is case-sensitive.
Parameters
• Parm1
Specifies the character pattern that you want to search for in the column.
• Parm2
Specifies the character pattern that you want to use for replacing the searched pattern. If Parm2 is absent, Parm1 is the name of a CSV file that contains a list of values to be replaced. Place the CSV file in the same directory as gtfdm.exe.
Applies to:
Character
Example 1:
Case 1 (When Parm2 is present)
When the pattern
Ab
is found in the column
, it is replaced with
23
. Similarly, if
a
is found in the column
, is also replaced by
23
. The following table shows the usage:
 Table Column Function Parm1 Parm2 Parm3 Parm4 PERSONS ADDRESS_1 REPLACE Ab 23 PERSONS ADDRESS_2 REPLACE a 23
Example 2:
Case 2 (When Parm2 is absent)
As Parm2 is absent, Parm1 is the name of a CSV file that contains a list of values to be replaced. The following table shows the usage:
 Table Column Function Parm1 Parm2 Parm3 PERSONS ADDRESS_1 REPLACE replacelist.csv
The following snippet shows the example of a CSV file
replacelist.csv
, which must be saved in the
gtfdm.exe
directory.
```Replace.csv
st, CD
ing, EH```
Note:
Fast Data Masker processes the replace values in the CSV file sequentially from top to bottom, so the unmasked values in the CSV replacement files are order dependent.
RIDCHECKDIGIT
The RID function masks an existing Recipient Identification Number.
Parameters:
None
Applies To:
Numeric, Character
Example:
 Table Column Function Parm1 Parm2 Parm3 Parm4 PERSONS RID RIDCHECKDIGIT
RJUST
The RJUST function strips blanks from the right of the string and right justifies the column in the string, padding the left with blanks (default) or the value defined in Parm1.
Note:
This function is most likely to be used in conjunction with the SUBSTR function.
Parameters
• Parm1 (Optional)
Specifies the value that you want to use for padding.
Applies to:
Character
Example:
LAST_NAME
(for example,
Smith
) as something like
55555SMITH
. The following table shows the usage:
 Table Column Function Parm1 Parm2 Parm3 Parm4 KeepNulls PEOPLE LAST_NAME RJUST 5 N
RUT
The RUT function generates a Chilean Social Security Number.
Parameters:
None
Applies to:
Character
Example:
The
SOCIAL_SECURITY_NUMBER
column is masked with a Chilean RUT value. The following table shows the usage:
 Table Column Function Parm1 Parm2 Parm3 Parm4 PERSONS SOCIAL_SECURITY_NUMBER RUT
SEQCHAR
The SEQCHAR function represents a sequence using BASE62 numbers. The start key is converted to a BASE62 number which becomes the start value of the sequence.
Parameters
• Parm1
Specifies an alphanumeric key to initialize the sequence.
Applies to:
Character
Example:
For example, the key aaaaaaaaaa is converted to the BASE62 number 1524750604, which is converted to the shorter BASE62 number 1fBhHg. The CONFIRMED_EMAIL column is now masked with the values 1fBhHg, 1fBhHh, 1fBhHi, and so on. The following table shows the usage:
 Table Column Function Parm1 Parm2 Parm3 Parm4 KeepNulls PERSONS CONFIRMED_EMAIL SEQCHAR aaaaaaaaaa N
SEQLOV
The SEQLOV function masks the column values with sequentially selected values from a seed list. This can come from a seed file (.txt) or database-based seed table.
Parameters
• Parm1
This can be one of two values:
• The name of the seed file, as a .txt file. You can create multi-column seed file seedlists from multiple seed files (define one seed file for each column).
By default, the location of seed files is
. You can define this within FDM with the dialog at
Settings/Set Default Directories
.
• The name of a seedlist from a database table (defined by the Masking Option SEEDTABLE). This is in the form of a string, which is present in the RD_REF_ID column (by default, the first column) of the database table, for all the seedlist values.
• Parm2
Specifies the index of the column in the table you define in Parm1, from which you want to generate a masking value. Default value = 1, i.e. the first column to the right of that which contains the name of the seedlist.
• (Optional) Parm4
Specifies a sequence identifier. This prevents repetition of values from seedlists used in multiple instances of SEQLOV in a masking job. Instances of SEQLOV with the same seedlist and the same sequence identifier, pick unique values from the seedlist across all instances. Without this identifier, columns masked with the same seedlist may be masked with repeated values from that seedlist.
Applies to:
Character, Number, and Date
Example:
The following table shows the usage for single-column seedlists.
 Table Column Function Parm1 Parm2 ADDRESS CITY SEQLOV uktowns.txt 1 ADDRESS STATE_PROVINCE SEQLOV ukpostcode3.txt 1 PERSONS LAST_NAME SEQLOV lastnameindian.txt 1
SEQLOV1
The SEQLOV1 function generates sequential values from a database-based seed table (that contains for example, addresses, cities, states/provinces, ZIP code, etc) that are valid for one existing value from these columns.
Parameters
• Parm1
Specifies the name of the seedlist in the table that you define with the Add Seedlists from a database table). This is in the form of a string, which must be present in the Seedlist Name Column (TDM Portal) or in the first of the columns defined by SEEDTABLECOLUMNS (Fast Data Masker), for all entries in the seedlist you want to use. In the provided
scramble
database, this column is RD_REF_ID.
• Parm2
Specifies the index of the column in the table you define in Parm1, from which you want to generate a masking value. Default value = 1, i.e. the first column to the right of the column that contains the name of the seedlist.
• Parm3
Specifies the column in the table to be masked, from which to get the existing value that the function matches in the seed table.
The column in the seed table against which this column value is matched, is the first of the Seedlist Value Column(s) you define on the Masking Configuration page (TDM Portal), or the second of those defined by the Masking Option SEEDTABLECOLUMNS (Fast Data Masker).
• (Optional) Parm4
Specifies the first
n
characters from the comparison column defined in Parm3, to compare with the seed table. This filters the seedlist defined by Parm2, from which TDM picks a value for masking.
Applies to:
Character, Numeric, and Date
Example:
The database table below shows part of a seed table. For each column in your data to mask, FDM generates a seedlist that consists of rows from the seed table, with the value of Parm1 (in this case
) in the Seedlist Name column (equivalent to RD_REF_ID in the
scramble
database). Parm3 is the column 'ZIP_CODE' - this subdivides the
seedlist into a sub-list, in which all values of 'REF_COL' (in this case postal codes) match the value of 'ZIP_CODE' in the row to mask (if Parm4 is defined as
n
, this match only applies to the first
n
characters of 'ZIP_CODE'). The value of Parm2 defines from which column of this sub-list, FDM selects a value.
Seed Table 'SEED' (showing part of 'US_ADDRESSES' seedlist)
 SEEDLIST_NAME REF_COL VALUE_COL_1 VALUE_COL_2 VALUE_COL_3 VALUE_COL_4 VALUE_COL_5 US_ADDRESSES 99627 13 Anderson Rd Mc grath Yukon-koyukuk AK US_ADDRESSES 99682 86 West Ave Tyonek Kenai peninsula AK US_ADDRESSES 99929 22 Park Rd Wrangell Wrangell-perterburg AK US_ADDRESSES 80216 2 Park St Denver Denver CO US_ADDRESSES 80104 145 Smith St Castle Rock Douglas CO
Examples of use of masking function
SEQNUMBER
The SEQNUMBER function updates each row with a user-defined sequence.
Parameters
• Parm1 (Optional)
Specifies the start value for the sequence. If Parm1 is not provided, the sequence starts at 1.
Applies to:
Numeric
Example:
If Parm1 is
10
, the first row is updated for this column with a value
10
, the next row with
11
, and so on. The following table shows the usage:
 Table Column Function Parm1 Parm2 Parm3 Parm4 KeepNulls PERSONS ID SEQNUMBER 10 N
SHUFFLE
The SHUFFLE function shuffles the values in the specified column for an entire table. The function creates a seed file by writing the value of each row for the column to a list of values. It then uses the SEQLOV function to overwrite the values in the database.
The SHUFFLE function lets you write the list of values to the following types:
• FILE: Fast Data Masker knows to write to a file if a . (dot) is present in the Parm1 value.
• Database: If Parm1 does not have a . (dot) value, the category name is stored in the database seed table.
Note:
Do not run this function in maps with cross-references.
Parameters
• Parm1
Specifies the category in the seed table in which your list of values is saved. For example,
containing
,
City
, and
Postcode
.
• Parm2
Specifies the column in the seed table where you want to place the value. For example, entering
1
selects
RD_REF_1
, which is
in the example below.
For this function to run, you need to use the following masking options:
• SEEDTABLECONNECT=
connectscramble.txt
• SEEDTABLE=
gtsrc_reference_data
• SEEDTABLECOLUMNS=
RD_REF_ID,RD_REF_VALUE1,RD_REF_VALUE2
Note:
Do not use the name of an existing seed table.txt file as it is overwritten each time the function is run.
Applies to:
Character and Number
Example:
The following table shows the usage:
SQLFUNCTION
The SQLFUNCTION function lets you use a native database function or a user-defined function. You can also use this function to use normal SQL operators to process combinations of other columns. All SQL functions with the exception of aggregate functions must work.
Parameters
• Parm1
Specifies the SQL function or SQL statement.
• Parm2 (Optional)
Specifies whether to apply SQLFUNCTION as one SQL update statement at the end of the masking process. This is far more efficient than processing one row at a time. To do so, select this parameter.
Applies to:
Character, Numeric, and Date
Example:
Parm1=
first_name || ' '|| last_name
. This example concatenates the first name, space, and last name.
Parm1=
mynumberformat(HHNO)
. This example passes HHNO into the database function. The returned value populates the masked column.
The following table shows the usage:
 Table Column Function Parm1 Parm2 Parm3 Parm4 KeepNulls C_BO_PRTY2 FST_NM HASHLOV FIRSTNAME 1 Y C_BO_PRTY2 ORIG_FST_NM HASHLOV FIRSTNAME 1 PRTY_FST_NM Y C_BO_PRTY2 LAST_NM HASHLOV LASTNAME 1 Y C_BO_PRTY2 ORIG_LST_NM HASHLOV LASTNAME 1 PRTY_LAST_NM Y C_BO_PRTY2 FULL_NM SQLFUNCTION ORIG_LST_NM || ',' || ORIG_FST_NM || '' || ORIG_MD_NM N
Note:
You can also use SQLFUNCTION to add extra columns in a mapping CSV. These are as follows:
• UPDATE: If Update=N (default=Y), then the mask for this row is not applied and is put in the memory to be used when required.
• USEMASKEDVALUES: If Use Masked Values=Y (default=N), then columns specified in SQLFUNCTION are tested to see whether they have been masked earlier in the mapping. If so, the masked value is used.
SUBSTR
The SUBSTR function extracts a sub-string from an existing string based on the start position and the length specified for the sub-string.
Parameters
• Parm1
Specifies the position from where to start the extraction.
• Parm2
Specifies the number of characters to extract from the string.
Applies to:
Character
Example:
The SUBSTR function extracts sub-strings from the strings in the
FIRST_NAME
column. The extraction starts from the position one with the number of characters to extract as four. For example, if the original string in the column is
Johnson
, the function in this case extracts the sub-string
John
. The following table shows the usage:
 Table Column Function Parm1 Parm2 Parm3 Parm4 KeepNulls PEOPLE FIRST_NAME SUBSTR 1 4 Y
Note:
In case of a flat file, the Table column remains empty.
TIN
The TIN function generates a United States Tax Identification Number.
Parameters
• Parm1 (Optional)
Specifies the separator character that you want to use.
Applies to:
Character and Numeric
Example:
The
TAXID
column in the table
PEOPLE
is masked with a generated US Tax Identification Number. The following table shows the usage:
 Table Column Function Parm1 Parm2 Parm3 Parm4 PEOPLE TAXID TIN
TRANSLATE
The TRANSLATE function searches the column values for every single character specified in Parm1 and replaces it with the corresponding character (sequentially) specifies in Parm2. TRANSLATE is a character-by-character operation. This function is case-sensitive.
Parameters
• Parm1
Specifies the characters to be searched in the column.
• Parm2
Specifies the corresponding characters to be replaced.
Applies to:
Character and Numeric
Example:
All instances of
a
in the column
FIRST_NAME
are translated to
x
, and all instances of
1
in the column
MEMBERSHIP_ID
are translated to
6
. The following table shows the usage:
 Table Column Function Parm1 Parm2 Parm3 Parm4 PERSONS FIRST_NAME TRANSLATE a x PERSONS MEMBERSHIP_ID TRANSLATE 1 6
TRANSPOSE
The TRANSPOSE function consistently converts one character to another character; for example, a to c, b to d, and so on. Set PARM1 to a number to act as a key.
Parameters
• Parm1
Specifies the key of the transposition.
• Parm2
Applies to:
Character
Example:
If
ab
is found in the column value and the Parm1 is set to
4
, the value is converted to
ef
. That is, every
a
character is translated to
e
and every
b
character is translated to
f
based on the key value in Parm1. The following table shows the usage:
 Table Column Function Parm1 Parm2 Parm3 PERSONS FIRST_NAME TRANSPOSE 4
TRIM
The TRIM function removes all the leading and trailing spaces from the specific column. After you use the TRIM function, you can use your other masking functions as required.
Note:
For database masking, the TRIM function is applicable only for non-date columns. Also, we recommended that you do not use the TRIM function on the column where the width of the column is explicitly defined.
Parameters:
None
Applies to:
Character
Example:
Leading and trailing spaces in the
LAST_NAME
column of the
PERSONS
table are trimmed. The following table shows the usage:
 Table Column Function Parm1 Parm2 Parm3 Parm4 KeepNulls PERSONS LAST_NAME TRIM Y
Note:
For mainframe, you can use the COMBINEVALS, SETSTR, or ASSIGNSTR functions to get the same functionality that the TRIM function provides. For more information about how to use these mainframe-specific functions, see their corresponding documentation on the mainframe.
TRUNCATE
The TRUNCATE function truncates all the data in the table.
Parameters:
None
Applies to:
Character, Date, and Numeric
Example:
Truncating the data executes a fast delete of all the data in the table. The following table shows the usage:
 Table Column Function Parm1 Parm2 Parm3 Parm4 KeepNulls PERSONS LAST_NAME TRUNCATE N
UNIQUEBELGIANID
The UNIQUEBELGIANID function generates a unique Belgian National Identification Number sequentially.
Parameters:
• Parm1 — Start Date of Birth
Specifies a start date of birth in yyyy-MM-dd format.
• Parm2 — End Date of Birth
Specifies an end date of birth in yyyy-MM-dd format.
• Parm3 — Gender
Specifies the gender as one the following: U (unisex) or M (male) or F (female).
Applies To:
Numeric, Character
Example:
 Table Column Function Parm1 Parm2 Parm3 Parm4 PEOPLE BELGIAN_ID_COLUMN UNIQUEBELGIANID 1991-01-01 2001-12-31 U
UNIQUEBSN
The UNIQUEBSN function consistently masks the given column values with a unique Dutch BSN Number.
Parameters:
None
Applies to:
Character and Numeric
Example:
The following are the examples:
• 123
to
661165544
• 456
to
384090095
• 123
to
661165544
• 456
to
384090095
UNIQUECPR
The UNIQUECPR function consistently masks the given column values with a unique Danish CPR Number.
Parameters:
None
Applies to:
Character
Example:
The following are the examples:
• 294398775
to
101500314
• 438425710
to
101500411
• 294398775
to
101500314
• 438425710
to
101500411
UNIQUEFINNISHID
The UNIQUEFINNISHID generates a sequential unique Finnish ID.
Parameters:
None
Applies to:
Character
Examples:
• 010120-029J
• 010120-001M
• 010120-002N
• 010120-003P
• 010120-004R
UNIQUESPANISHCIF
The UNIQUESPANISHCIF function generates a unique Spanish CIF. A Spanish CIF is a 9-character value (one letter, followed by 7 numbers, followed by one checksum letter/number).
Parameters:
• Parm1
Prefix to CIF. This value forms the start of any CIFs you generate.
If the prefix is invalid (i.e. does not conform to the CIF format, or is too long), the function ignores all characters from the invalid character onwards.
Applies to:
Character
Examples:
 Parm1 First Output CIF A0000000G C86 C8600000K HG29 H0000000A
UNIQUESPANISHID
The UNIQUESPANISHID function generates a unique Spanish ID.
Parameters:
• Parm1
NIF/NIE
Specifies whether the Spanish ID is of the type NIF or NIE. Takes argument True (NIF) / False (NIE)
Applies to:
Character
UNIQUETURKISHID
The UNIQUETURKISHID function generates a unique 11-digit Turkish Identification Number. If Parm2 and Parm3 are provided, they define the upper and lower bounds; if valid.
Parameters
• Parm1
Specifies whether you want to generate a sequence. Values: Y or N. Default:
N
.
• Parm2
Defines the start value of the sequence. Default:
100000000
.
• Parm3
Defines the end value of the sequence. Default:
999999999
.
Applies to:
Numeric
Example 1 :
Parm1: Y, Parm2:
100000000
, Parm3:
100000010
• 10000000078
• 10000000146
• 10000000214
• 10000000382
• 10000000450
• 10000000528
• 10000000696
• 10000000764
• 10000000832
• 10000000900
Example 2:
Parm1: N, Parm2: optional, Parm3: optional.
• 67721221312
• 48512950116
• 67358262436
• 36834234250
• 49588725660
• 34986642282
• 38676152648
• 49218435488
• 37194605476
• 19870583810
UNIQUETURKISHTAXID
The UNIQUETURKISHTAXID function generates a unique 10-digit Turkish Tax Identification Number.
Applies to:
Numeric, Character
Parameters:
• Parm1
Specifies whether you want to generate a sequence. Values: Y or N. Default: N.
• Parm2
Defines the start value of the sequence if Parm1 is Y. Default: 1111111111.
• Parm3
Defines the end value of the sequence if Parm1 is Y. Default: 9999999999.
USPHONE
The USPHONE function masks the column with an auto-generated 7-digit US phone number of the format xxxxxxx.
Parameters:
None
Applies to:
Character and Numeric
Example:
The
USPHONE
column in the
PERSONS
table is masked with auto-generated 7-digit US phone numbers. The following table shows the usage:
 Table Column Function Parm1 Parm2 Parm3 Parm4 PERSONS USPHONE USPHONE
USPHONE(10)
The USPHONE(10) function masks the column with an auto-generated 10-digit US phone number of the format xxx-xxx-xxxx.
Parameters:
None
Applies to:
Character and Numeric
Example:
The
USPHONE
column in the
PERSONS
table is masked with auto-generated 10-digit US phone numbers. The following table shows the usage:
 Table Column Function Parm1 Parm2 Parm3 Parm4 KeepNulls PERSONS USPHONE USPHONE(10)
USZIP
The USZIP function masks the columns with an auto-generated 5-digit US ZIP code.
Parameters:
None
Applies to:
Character and Numeric
Example:
The
ZIP_CODE
column in the
PERSONS
table is masked with auto-generated 5-digit US ZIP codes. The following table shows the usage:
 Table Column Function Parm1 Parm2 Parm3 Parm4 KeepNulls PERSONS ZIP_CODE USZIP
USZIP+4
The USZIP+4 function masks the columns with an auto-generated 9-digit US ZIP code (format: xxxxxxxxx).
Parameters:
None
Applies to:
Character and Numeric
Example:
The
ZIP_CODE
column in the
PERSONS
table is masked with auto-generated 9-digit US ZIP codes. The following table shows the usage:
 Table Column Function Parm1 Parm2 Parm3 Parm4 KeepNulls PERSONS ZIP_CODE USZIP+4
VALIDSIN
The VALIDSIN function tests for a valid Canadian Social Insurance Number (SIN). If the data is a valid SIN, then it is replaced with a new value, using an optional Parm1 as a separator for each of the three sets of digits. Otherwise, it leaves the number
as is
.
Some examples of a bad SIN include:
• The SIN is not a number after the separator characters are removed.
• It is not 9 digits in length.
• The number starts with 0 or 8.
Parameters
• Parm1 (Optional)
Specifies the separator character which must match the existing data or all numbers are flagged as invalid and no changes are made.
Applies to:
Character and Numeric
Example:
The following table shows the usage:
 Table Column Function Parm1 Parm2 Parm3 Parm4 KeepNulls PERSON SIN_NUMBER VALIDSIN N
VALIDSSN
The VALIDSSN function identifies whether a column contains a valid SSN (United States Social Security Number). If so, the function masks it with a generated SSN.
Parameters
• Parm1
Specifies the separator character.
Applies to:
Character and Numeric
Example:
If the column
ID
in the table
PEOPLE
contains a valid SSN, it is replaced with a random SSN. The following table shows the usage:
 Table Column Function Parm1 Parm2 Parm3 Parm4 PEOPLE ID VALIDSSN
VALIDSSNSUB
The VALIDSSNSUB function identifies whether the first 9 characters of a column contain a valid SSN (United States Social Security Number). If so, the function masks the valid SSN with a generated SSN.
Parameters
• Parm1
Applies to:
Character and Numeric
Example:
If a valid SSN is found in the first nine characters of the column
ID
in the table
PEOPLE
, it is replaced with a random SSN.
The following table shows the usage:
 Table Column Function Parm1 Parm2 Parm3 Parm4 PEOPLE ID VALIDSSBNSUB
VALIDTIN
The VALIDTIN function identifies whether the column contains a valid TIN (United States Tax Identification Number). If so, the function masks it with a generated TIN.
Parameters
• Parm1
Specifies the separator character.
Applies to:
Character and Numeric
Example:
If the column
ID
in the table
PEOPLE
contains a valid TIN, it is replaced with a random TIN. The following table shows the usage:
 Table Column Function Parm1 Parm2 Parm3 Parm4 PEOPLE ID VALIDTIN
VARIENCE
The VARIENCE function generates values based on Parm1 (% value) and then adds them to or subtracts them from the column values.
Parameters
• Parm1
Specifies the percentage variance (1-99).
• Parm2 (Optional)
Specifies the minimum permitted value.
• Parm3 (Optional)
Specifies the maximum permitted value.
Applies to:
Numeric
Example:
If the column value is
100
, then Parm1 applies
60%
variance and a random number is generated between
40
and
160
. However, Parm2 (minimum permitted value) and Parm3 (maximum permitted value) ensure that the generated random value lies in the range
50
through
150
40
through
160
.
The following table shows the usage:
 Table Column Function Parm1 Parm2 Parm3 Parm4 PERSONS CREDIT_SCORE VARIENCE 60 50 150
VISACARD
The VISACARD function generates a random VISA credit card number.
Parameters:
None
Applies to:
Character and Numeric
Example:
Generates a valid VISA credit card number, such as
4012888653017322
. The following table shows the usage:
 Table Column Function Parm1 Parm2 Parm3 PERSONS CREDIT_CARD VISACRAD
XMLREPLACE
The XMLREPLACE function replaces values embedded in XML files in the database.
Parameters:
• Parm1
Specifies the value to replace.
• Parm2
Specifies the value to use as a replacement for Parm1.
Applies to:
Character and Numeric
Example:
Replaces the value of XML tag 'first_name' with the value of XML tag 'last_name'.
 Table Column Function Parm1 Parm2 PEOPLE XML_DATA XMLREPLACE /TABLES/PEOPLE/first_name /TABLES/PEOPLE/last_name