Masking Functions and Parameters

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. 
tdm47
HID_Mask_Functions_Params
This page includes information about the supported masking functions and their required parameters.
 
Table of Contents...
 
 
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
To mask digit 
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.
ADD
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
 days to the existing date. To address this issue, download and extract the 
sqljdbc_4.0.2206.100_enu.exe
 file from https://www.microsoft.com/en-in/download/details.aspx?id=11774. Then, copy the 
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 
SHIP_TO_ADDRESS_ID
 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
     
ADDDAYS
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
 adds between 
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
     
ADDPERCENT
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
     
ADDRANDOM
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
     
ADDRANDOMDAYS
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
     
ADDRANDOMHOURS
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
ADDRANDOMMINUTES
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
ADDRANDOMSECONDS
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
ADDRANDOMYEARS
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
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
 column is masked to 
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 numbers.
 
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 
Use Masked Values
 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.
 
Applies to:
 Character and Date
 
Example:
 The age 
52
 (DOB 
9/5/1958
) remains 
52
, but could be adjusted, for example, by adding 
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
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.
 
Applies to:
 Character and Date
 
Example:
 The date of death 
10/9/2009
 remains at 
1
 year, but could be adjusted by adding 
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
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.
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
 is masked with the value 
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
FORMATENCRYPT
The FORMATENCRYPT function consistently masks the given column values by preserving the original format and keeping the unique values. 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:
  • (Optional) PARM1
    Specifies the number of start characters to ignore.
  • (Optional) PARM2
    Specifies the number of end characters to ignore.
  • (Optional) PARM3
    (If PARM1 and PARM2 are not set) Specifies the number of start characters to mask.
  • (Optional) PARM4
    (If PARM1 and PARM2 are not set) Specifies the number of end characters to mask.
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
    . Additionally, verify that the key does not start with the character
    a
    .
 
Applies to:
 Characters and Numbers
 
Examples:
 The following are the examples:
  •  
    Denzel 
    to 
    Yetqmo
     
  •  
    Tellus 
    to 
    Oerccv
     
  •  
    Dervorgilla 
    to 
    Yexmwuzwzws
     
  •  
    59109.34
     to 
    59821.00
     
  •  
    28727.34
     to 2
    1297.00
     
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:
 None
 
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
FORMATMASK
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
The FORMATVIN function masks a 17-digit vehicle identification number (VIN). The function leaves the first 8 characters of the VIN number as is. The last 9 characters are consistently changed. The 9th of the 17 characters is a check digit, which is calculated from the other characters in the VIN. If the data is null or the original character string is not 17 characters in length, the function returns the input unchanged. The function produces unique values as long as the original values are also unique.
 
Parameters: 
None
 
Applies to:
 Characters
 
Example:
 The values in the 
VEH_ID
 column are masked by using FORMATVIN; for example, 
2HGBH52JXMN210295
 becomes 
2HGBH52J6HV999318
. 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
 column masks existing values as 
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
 column is masked to 
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
 column are masked to 
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
 column is masked to 
5599197827275710
. Note that the first two digits remain the same. The following table shows the usage:
Table
Column
Function
Parm1
EMPLOYEE
CARD_NUMBER
HASHCARD
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
 column is masked to
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
 are consistently masked, from 
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: 
    address.1.txt
    address.2.txt
    .
  • 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
 masking option in the 
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
     addresses are returned.
  • 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
    , your list then contains all addresses starting with 
    OX29
    .
Masking Option
  •  
    (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 
Employee_Address
.
It takes the value for the column defined in "Override SQL" (
ADDRESS_ID
) 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 (
    EMPLOYEE_ADDRESS
    ) 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
     masking option in the 
    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
 column is masked to 
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 
adfere
.
 
Parameters:
 None
 
Applies to:
 Character and Numeric
 
Example:
 The phone number 
1712441
9639
 
 in the 
PHONE
 column is masked to 
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
HASHSPANISHID
The HASHSPANISHID function consistently masks Spanish ID numbers (NIF or NIE).
 HASHSPANISHID does not mask CIF numbers. The function skips CIF numbers.
 
Parameters:
 None
 
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
 column are masked appropriately:
  •  
    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
 is masked to 
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
 column is masked to 
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
 column is masked to 
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
MASKBELGIANID
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
PARTMASK
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:
 Numerics are masked in the 
POSTCODE
 column of the 
ADDRESS
 table. For example, the postcode 
OX29 4TP
 becomes 
OX34 8TP
. The following table shows the usage:
Table
Column
Function
Parm1
Parm2
Parm3
Parm4
ADDRESS
POSTCODE
PARTMASK
N
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
POSITIONMASK
The POSITIONMASK function masks a value based on positional rules, as defined in Parm1.
 
Parameters:
 
  • Parm1
    Specifies the rules for each position built from the following formula:
    • RDXXXL: Random digit at position XXX from left.
    • RDXXXR: Random digit at position XXX from right.
    • RAXXXL: Random alphabet at position XXX from left.
    • RAXXXR: Random alphabet at position XXX from right.
    • RCXXXL: Random alphanumeric character at position XXX from left.
    • RCXXXR: Random alphanumeric character at position XXX from right.
    • F1XXXL: Fixed digit 1(for example) at position XXX from left.
    • F1XXXR: Fixed digit 1(for example) at position XXX from right.
    • FEXXXL: Fixed alphabet E(for example) at position XXX from left.
    • FEXXXR: Fixed alphabet E(for example) at position XXX from right.
 
Notes
 
  • If a rule is not given for any position, the old value is retained after the masking.
  • If the old value is null or all blanks, skip the row and move to the next row.
  • All of the rules for a column must be set in one row of the CSV file. To separate each positional rule, use a hyphen (-) without a space.
 
Applies to:
 Character
 
Example:
 The 
PHONE_NUMBER
 column in the table 
PEOPLE
 has first three characters masked with the fixed value 
9
, with the remaining digits remaining as original values. Therefore, the resulting masked value is 
999XXXXXX
, where 
X
 is the existing value. For example, 
1235553283
 becomes 
9995553283
. The following table shows the usage:
Table
Column
Function
Parm1
Parm2
PEOPLE
PHONE_NUMBER
POSITIONMARK
F9001L-F9002L-F9003L
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 
US_ADDRESSES
 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 (
ADDRESSES
) but not in the seed data category (
US_ADDRESS
), add a line to the seed data table for that category with default values (
RD_REF_VALUE ‘DEFAULT’
).
The following table shows the usage:
Table
Column
Function
Parm1
Parm2
Parm3
Parm4
ADDRESSES
ADDRESS2
RANDLOV1
US_ADDRESS
3
POSTAL_CODE
73 Main St
ADDRESSES
CITY
RANDLOV1
US_ADDRESS
5
POSTAL_CODE
New York
ADDRESSES
STATE_PROVINCE
RANDLOV1
US_ADDRESS
4
POSTAL_CODE
New York
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
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 
ADDRESS_1
, it is replaced with 
23
. Similarly, if 
a
 is found in the column 
ADDRESS_2
, 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
 reaplcelist.csv
The following snippet shows the example of a CSV file 
replacelist.csv
, which must be saved in the 
gtfdm.exe
 directory.
Replace.csv
Te, AD
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:
 The example masks 
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 
      C:\Program Files\Grid-Tools\FastDataMasker\seedtables
      . 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 Masking Option SEEDTABLE (Fast Data Masker), or on the Masking Configuration page (TDM Portal - see 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 
US_ADDRESSES
) in the Seedlist Name column (equivalent to RD_REF_ID in the 
scramble
 database). Parm3 is the column 'ZIP_CODE' - this subdivides the 
US_ADDRESSES
 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
 
Table
Column
Function
Parm1
Parm2
Parm3
Parm4
Source of resultant masking value
ADDRESSES
ADDRESS2
SEQLOV1
US_ADDRESSES
3
ZIP_CODE
4
SEED.VALUE_COL_2, where SEED.REF_COL matches first 4 chars of ADDRESSES.ZIP_CODE
ADDRESSES
CITY
SEQLOV1
US_ADDRESSES
4
ZIP_CODE
3
SEED.VALUE_COL_3, where SEED.REF_COL matches first 3 chars of ADDRESSES.ZIP_CODE
ADDRESSES
STATE_PROVINCE
SEQLOV1
US_ADDRESSES
6
ZIP_CODE
2
SEED.VALUE_COL_4, where SEED.REF_COL matches first 2 chars of ADDRESSES.ZIP_CODE
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, 
    MY_ADDRESSES
     containing 
    Address
    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 
    ADDRESS
     in the example below.
 
Masking Options
 
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:
Table
Column
Function
Parm1
Parm2
Parm3
Parm4
KeepNulls
ADDRESSES
ADDRESS_1
SHUFFLE
MY_ADDRESS
1
N
ADDRESSES
CITY
SHUFFLE
MY_ADDRESS
2
ADDRESSES
POSTCODE
SHUFFLE
MY_ADDRESS
3
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
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
 instead of 
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