Azure SQL Database does not display as the Dynamic Mask pattern after using trim()

Question

I am using Azure SQL database and some of the table columns are using Dynamic mask like this:

CREATE TABLE PartialMask
(
    ID INT IDENTITY (1,1) PRIMARY KEY NOT NULL,
    Name VARCHAR(255) MASKED WITH (FUNCTION = 'partial(2, "XXXXXX",2)') NULL,
    Comment NVARCHAR(255) MASKED WITH (FUNCTION = 'partial(5, "XXXX", 5)') NOT NULL
);

For example, the raw data for name is “ABCDE”, then the query will show “ABXXXXXXDE”

However, when we use trim()for the name column, like

Select trim(name) From PartialMask

I will get “xxxx“.

Did you have the same issue and any solutions?

Hi Vinci, If the answer is helpful for you, you can accept it as answer( click on the check mark beside the answer to toggle it from greyed out to filled in.). This can be beneficial to other community members. Thank you. — Leon Yue, Aug 13 '20 at 02:55

Leon Yue · Answer 1 · 2020-08-12T02:36:56.003

I tested and get the same issue:

Here's the reason:

Arguments

characters Is a literal, variable, or function call of any non-LOB character type (nvarchar, varchar, nchar, or char) containing characters that should be removed. nvarchar(max) and varchar(max) types aren't allowed.

Ref: https://learn.microsoft.com/en-us/sql/t-sql/functions/trim-transact-sql?view=sql-server-ver15#arguments

trim() doesn't support the characters which get from the Dynamic data masking function. Dynamic data masking is to protect the data, if we don't have the permission, we can not see the full data.

Azure SQL Database does not display as the Dynamic Mask pattern after using trim()

1 Answers1