Menu
I'm using SQL Server 2008.
I would like to character mask the output data of a query.
This is my data from a column on a table when doing a select:
column1
384844033434
743423547878
111224678885
I would like an output like this:
column1
384xxxxxx434
743xxxxxx878
111xxxxxx885
How can I do this?
767
To mask a column, use the SQL syntax MASKED [AS {BASIC | NULL | 0 | ' '}] as a column attribute on the CREATE TABLE, CREATE TABLE AS SELECT, or ALTER TABLE ALTER COLUMN statement. The MASKED attribute marks the column as being a protected resource.
Users with SELECT permission on a table can view the table data. Columns that are defined as masked, will display the masked data. Grant the UNMASK permission to a user to enable them to retrieve unmasked data from the columns for which masking is defined.
A proper access control policy should still be used to limit update permissions. Using SELECT INTO or INSERT INTO to copy data from a masked column into another table results in masked data in the target table. Dynamic Data Masking is applied when running SQL Server Import and Export.
Use a combination of STUFF , REPLICATE and CHARINDEX . CHARINDEX will give you the position of a particular string inside another one (the first by default). REPLICATE will repeat a string N amount of times, we use this for the mask.
This also works for masking output when retrieve.
SELECT CONCAT(SUBSTRING(column1, 1, 3),'xxxxxx',SUBSTRING(column1, 10, 3)) FROM tableName;
If the value length is not fixed, can use the below.
SELECT CONCAT(SUBSTRING(column1, 1, 3),'xxxxxx',SUBSTRING(column1, LEN(column1)-2, 3)) FROM tableName;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
