Skip to main content
Delphix

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:

  1. Open the tab Rule Set
  2. Click on the Edit icon for a specific table:

​​​UI Edit Rule Set (green pen).png

  1. Select Custom SQL.

UI Edit Custom SQL 1.png

  1. Edit the SQL statement.
    For examples see Casting Examples below.

UI Edit Custom SQL 2.png

  1. Click Save.

Note

Note:

 When editing the statement, keep in mind:

  • The SQL statement needs to be valid SQL or it will fail to save. 
  • For large tables with many masked columns, the SQL could be very long. 
  • If the masked columns change in this table, the SQL needs to be edited. 

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. 

Note

Note:

 Secure Shuffle is known not to work with casting as the algorithm code is different and the change is based on the data type on the masked column. 

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:

  1. float_expression
    • Is an expression of approximate numeric (float) data type with a decimal point.
  2. length
    • Is the total length. This includes a decimal point, sign, digits, and spaces.
    • The default is 10.
  3. 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:

  1. column
    • Is an expression of approximate numeric (float) data type with a decimal point.
  2. 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;