CODEWITHSUNDEEP
sqlsql-serversql-server-2016data-masking
Robotnik
Robotnik

Reputation: 3810

How to ignore empty strings with SQL Server 2016 Dynamic Data Masking?

I'm implementing SQL Server 2016 Data Masking as a test as part of an overarching effort to give an application a 'demo' mode which doesn't expose real data when showcased.

We have a mobile number field which follows the pattern 0411 222 333 (Australian mobile number), and the partial mask below correctly applies the standard mask we're after, which is to say 04XX XXX X33

ALTER TABLE Resource
ALTER COLUMN MobileNumber ADD MASKED WITH (FUNCTION='partial(2, "XX-XXX-X",2)')

However, if the value of the column is an empty string, (''), the mask is still applied, albeit with no data to mask:

Data Masking output, showing XX-XXX-X

Is there a way to ensure empty strings are ignored by the masking effort?

Upvotes: 0

Views: 533

Answers (1)

Eric
Eric

Reputation: 703

The MSDN article on this feature leaves a lot to be desired. But I did find another similar article pertaining to Azure that seems to answer your question.

Custom text:

Masking method which exposes the first and last characters and adds a custom padding string in the middle. If the original string is shorter than the exposed prefix and suffix, only the padding string will be used. prefix[padding]suffix

https://azure.microsoft.com/en-us/documentation/articles/sql-database-dynamic-data-masking-get-started/

Upvotes: 1

Related Questions