How to assign specific masked values to original values

If you have data that you wish to replace specific occurences of a value with a specific masked value then this can be easily achieved using the DataVeil User SQL Value mask.

Let’s illustrate with a simple example.

In the table ‘customer’, shown below, we have a column called ‘Location’ that we need to mask each specific value with a specific replacement value.


To achieve our objective we will need to:

  1. Create a lookup table
  2. Create a primary key on the lookup column in the lookup table
  3. Create a User SQL Value mask

Step 1) Create a lookup table


Create a table with two columns: an original value column and a column with the specific masked values to be used.

This has been done below in the table called ‘lookup_table’ with the two columns ‘original_value’ and ‘masked_value’. Of course, you can name these whatever you like.


Step 2) Create a primary key on the lookup column in the lookup table


This is really part of creating the table in the previous step but this step is crucial for performance (except for the smallest trivial tables) that I wanted to make sure it’s not overlooked.

Therefore, please make sure that you create a primary key (or unique index) on the ‘original_value’ column in the lookup table.

Step 3) Create a User SQL Value mask


Create a User SQL Value mask for the column to be masked, i.e. the ‘Location’ column in this example.


When the mask form opens, you will first need to create a JOIN to the lookup table. This is done under the mask’s ‘Join’ tab.


After selecting the required configuration from the combo boxes, please make sure to click on the ‘Add’ button so that the condition appears in the text box. It’s easy to forget to ‘Add’ the condition and later wonder why the join isn’t working. You can also add multiple conditions with each ‘Add’. This effectively AND’s these conditions together.


Finally, under the mask’s ‘User SQL Value’ tab, define the SQL code to perform the lookup.

Note that the column names are qualified with the ‘Table Alias’ values that are defined in the ‘Join’ tab. If you forget to use these aliases to qualify the column names then this will result in ‘ambiguous or unresolved column reference’ type error messages at run time.


Click ‘OK’ to close the mask form and the configuration is complete.


After running the project you can see below that the original values have been masked with the exact replacement values that were defined in the lookup table.