SQL Server : Update Table - Data Mask

Question

I have created a data mask that finds a 16 digit number anywhere within a string and replaces all but the last four characters with X's.

But instead of manually setting the string I need to update all data within a column located in a table. Please see my code so far:

DECLARE
    @NOTES AS VARCHAR(8000)

SET @NOTES = 'Returns the starting position of the first occurrence of a pattern in a specified  expression, 1234567891234567 or zeros if the pattern is not found, on all valid text and character data types'

SELECT 
   REPLACE(@NOTES, SUBSTRING(@NOTES, PATINDEX('%1%2%3%4%5%6%7%8%9%', @NOTES), 16), 'XXXXXXXXXXXX' + RIGHT(SUBSTRING(@NOTES, PATINDEX('%1%2%3%4%5%6%7%8%9%', @NOTES),16),4)) AS REPLACEMENT

Any help would be much appreciated :-)

SO, waht are you asking? "Do my work for me, I dont want to use my brain?" If you ahve a specific progblem (you start with "I have created" indicating it is finished work) then you totally fail to make sense in asking it. — TomTom, Sep 23 '14 at 18:36
You already said that. How could this repeated statement possibly help? — usr, Sep 23 '14 at 18:42
You need to cooperate and follow up on everything that has been said. What's the concrete problem that you're having? You stated your goal but what's preventing you from reaching it? — usr, Sep 23 '14 at 18:45
I'm trying to replace credit card numbers in a note field with this data mask, due to previous developers and data entry clerks, the string can appear anywhere, the only constant is that its 16 constant digits. — iggyweb, Sep 23 '14 at 18:47
Credit card numbers are not always 16 digits. *Your data* may only be 16 digits *today*, but I have cards in my wallet with 14, 15 and 16 digits on them. — alroc, Sep 23 '14 at 19:32
Granted, but this specific data is past data and all cards are 16 digits, this is a one off swipe, measures are in place for future data. — iggyweb, Sep 23 '14 at 19:46

Horaciux · Answer 1 · 2014-09-23T19:27:17.060

0

Create a function with your logic

CREATE FUNCTION MyMask(
    @NOTES  VARCHAR(8000))

    returns varchar(8000)

    BEGIN
    RETURN 
       REPLACE(@NOTES, SUBSTRING(@NOTES, PATINDEX('%1%2%3%4%5%6%7%8%9%', @NOTES), 16), 'XXXXXXXXXXXX' + RIGHT(SUBSTRING(@NOTES, PATINDEX('%1%2%3%4%5%6%7%8%9%', @NOTES),16),4))

    END

This is who you use it

update table
set field = dbo.myMask(field)
where some condition

edited Sep 23 '14 at 19:27

answered Sep 23 '14 at 18:49

Horaciux

6,322
2
22
41

I think, but not sure how to apply the code I have provided into a function. – iggyweb Sep 23 '14 at 18:52
Ok, give me a moment. I'll transform this in an usable code – Horaciux Sep 23 '14 at 18:53
check it and let me knok – Horaciux Sep 23 '14 at 19:04
Thank you, I ran the CREATE function and got the message Command(s) completed successfully and it is located under Scalar-valued Functions but I am try and run the UPDATE code I get the message 'myMask' is not a recognized built-in function name. – iggyweb Sep 23 '14 at 19:12
A slight typo, just realise it should have been 'update credit_cards set card_number = dbo.MyMask(card_number)' but the mask function now only deals with the first 16 characters irrespective of whether its a 16 digit number substring. – iggyweb Sep 23 '14 at 19:42
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/61789/discussion-between-iggyweb-and-horaciux). – iggyweb Sep 23 '14 at 19:47
I added son images in chat for you to verify that the function works exactly as your code. – Horaciux Sep 24 '14 at 00:48

score 0 · Accepted Answer · answered Sep 25 '14 at 10:51

The function provided by Horaciux, works re a static declared string, but the PATINDEX always sets to 0 when used in an update query.

The work around was to amend the implementation of the PATINDEX from PATINDEX('%1%2%3%4%5%6%7%8%9%' to PATINDEX('%[123456789]%' I have included the full function below:

CREATE FUNCTION [dbo].[MyMask](@NOTES VARCHAR(8000)) RETURNS VARCHAR(8000)
BEGIN
RETURN 
    REPLACE(@NOTES, SUBSTRING(@NOTES, PATINDEX('%[123456789]%', @NOTES), 16), 'XXXXXXXXXXXX' + RIGHT(SUBSTRING(@NOTES, PATINDEX('%[123456789]%', @NOTES),16),4))
END

I hope this is useful to others :-)

SQL Server : Update Table - Data Mask

2 Answers2