Skip to main content
Delphix

Algorithms - Casting Values before Masking

 

Applicable Delphix Masking Versions

  • 5.2.x
  • 5.1.x
  • 5.0.x
  • 4.x

Issues

This article describes how to cast values before masking. This might be needed when:

  • When there is a conversion error in the Masking Job.
  • When the data has leading  or trailing whitespaces.
  • When the data need to be standardized before - i.e. all UPPER case.

Note - the two last issues will not generate an error message

Resolution

These issues can be resolved by casting the value before it is masked. The Masking Engine will then work on the changed value.

To cast the value you need to change the Rule Set. On the Environment you like to change the Rule Set, follow these steps:

  1. Open the tab Rule Set
  2. Click on the green pen to edit the SQL for a specific table:
    • ​​​UI Edit Rule Set (green pen).png
  3. Select Custom SQL
    • UI Edit Custom SQL 1.png
  4. Edit the SQL statement (for examples see Casting Examples below)
    • UI Edit Custom SQL 2.png
  5. Save

Please note:

  • 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 shown below - STR and TO_CHAR.  There are other FUNCTIONS that can be used. 

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

Cast a number to a string (str)

If the issue is a number (and this usually throws an error), then casting the value to a string will likely resolve the issue. In the casting, it is possible to specifically change the data as needed using arguments. If more options are needed use "to_char" (see below).  

Google for full documentation for specific arguments. 

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)

A more versatile option to cast a numeric value or date to a string is the function 'TO_CHAR'.    

Google for full documentation for Number Formats. 

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 whitespaces. 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;