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.
tdm481
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
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
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.
  • 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
, 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
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
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
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:
      JavelinPassword
      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
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
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.
If the input entry contains more or fewer than 9 numbers, HASHSIN does not mask the value.
Parameters:
None
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
##
456
RR
789
846
##
812
RR
368
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
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, 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
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
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 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 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
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
<blank>
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
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