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 :-)

Answer 1

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 :-)