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?

Answer 1

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.