Algorithm: Casting Values Before Masking (KBA1580)
Issue
This article describes casting values before masking. You may need this:
- When there is a conversion error in the Masking Job.
- When the data has leading or trailing white spaces.
- When the data need to be standardized before - i.e. all UPPER case.
Note that the two last issues will not generate an error message.
Resolution
To resolve any issues cast the value before it is masked. The Masking Engine will work on the changed value.
To cast the value on the environment:
- Open the tab Rule Set
- Click on the Edit icon for a specific table:
.png?revision=1&size=bestfit&width=822&height=75)
- Select Custom SQL.
- Edit the SQL statement.
For examples see Casting Examples below.
- Click Save.
Casting Examples
Below are some examples of functions that cast (change the data type) of a value. In many cases, this can resolve issues where the algorithm is not supporting a specific data type or how the data is presented - i.e dates, decimal numbers, currencies etc.
Two functions are discussed below - STR and TO_CHAR. There are other FUNCTIONS that can also be used.
Cast a Number to a String (str)
If you encounter an error since the issue is a number, then casting the value to a string will likely resolve the issue. When casting, it is possible to specifically change the data as needed using arguments. If more options are needed use "to_char".
For more information for specific arguments, see Google.
The following arguments can be added:
- float_expression
- Is an expression of approximate numeric (float) data type with a decimal point.
- length
- Is the total length. This includes a decimal point, sign, digits, and spaces.
- The default is 10.
- decimal
- Is the number of places to the right of the decimal point. The decimal must be less than or equal to 16. If the decimal is more than 16 then the result is truncated to sixteen places to the right of the decimal point.
In Custom SQL change the masked column you like to cast - for example, maskMeCol:
..., maskMeCol, ...
to:
..., STR(maskMeCol) as maskMeCol, ...
Example:
SELECT ID, STR(maskMeCol) as maskMeCol from myTable;
Cast a Number to a String (to_char)
This is a versatile option to cast a numeric value or date to a string is the function 'TO_CHAR'.
For more information for Number Formats, see Google.
The following arguments are needed:
- column
- Is an expression of approximate numeric (float) data type with a decimal point.
- Number Format
- This is a string with codes that is composed of one or more number format elements.
- Google for full documentation for Number Formats.
In Custom SQL change the masked column you like to cast - for example, maskMeCol:
..., maskMeCol, ...
to:
..., TO_CHAR(maskMeCol,'[NUMBER_FORMAT]') as maskMeCol, ...
Example:
SELECT ID, TO_CHAR(maskMeCol,'90.99') as maskMeCol from myTable;
Cast to UPPER Case
To change all values in a masked column to UPPER case use this example. Changing all values to UPPER case can be needed if the algorithm is case sensitive and it is desired to have all values masked to the same value independently of case.
In Custom SQL change the masked column you like to cast - for example, maskMeCol:
..., maskMeCol, ...
to:
..., UPPER(maskMeCol) as maskMeCol, ...
Example:
SELECT ID, UPPER(maskMeCol) as maskMeCol from myTable;
Trim Whitespaces
The algorithm might be sensitive to leading and trailing white spaces. This is especially problematic when the datatype is CHAR or NCHAR as these will by default have trailing spaces.
In Custom SQL change the masked column you like to cast - for example, maskMeCol:
..., maskMeCol, ...
to:
..., LTRIM(RTRIM((maskMeCol)) as maskMeCol, ...
Example:
SELECT ID, LTRIM(RTRIM((maskMeCol)) as maskMeCol from myTable;