CREATE FUNCTION dbo.MaskCreditCardLike_12_18 (
@input NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @output NVARCHAR(MAX) = @input;
DECLARE @i INT = 1;
DECLARE @digitCount INT = 0;
DECLARE @startPos INT = 0;
WHILE @i <= LEN(@input)
BEGIN
DECLARE @c NCHAR(1) = SUBSTRING(@input, @i, 1);
IF @c LIKE '[0-9]'
BEGIN
IF @digitCount = 0
SET @startPos = @i;
SET @digitCount += 1;
END
ELSE IF @c IN ('-', '/', '*', 'X', 'x', ' ')
BEGIN
-- allowed separator, do not reset
CONTINUE;
END
ELSE
BEGIN
-- invalid char, reset
SET @digitCount = 0;
SET @startPos = 0;
END
-- Once we hit a 12- to 18-digit count, replace the original segment
IF @digitCount BETWEEN 12 AND 18
BEGIN
-- Now find the end of the segment
DECLARE @endPos INT = @i;
WHILE @endPos + 1 <= LEN(@input) AND
SUBSTRING(@input, @endPos + 1, 1) IN ('0','1','2','3','4','5','6','7','8','9','-','/','*','X','x',' ')
BEGIN
SET @endPos += 1;
END
DECLARE @replaceLength INT = @endPos - @startPos + 1;
SET @output = STUFF(@output, @startPos, @replaceLength, '$');
RETURN @output;
END
SET @i += 1;
END
RETURN @output;
END;
GO
CREATE FUNCTION dbo.ReplaceCodePattern_Exclude16Digit (
@input NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @output NVARCHAR(MAX) = @input;
DECLARE @cleaned NVARCHAR(MAX);
-- Clean string: remove -, /, *, X, space
SET @cleaned = REPLACE(@input, '-', '');
SET @cleaned = REPLACE(@cleaned, '/', '');
SET @cleaned = REPLACE(@cleaned, '*', '');
SET @cleaned = REPLACE(@cleaned, 'X', '');
SET @cleaned = REPLACE(@cleaned, ' ', '');
-- Check for any 16-digit numeric substring
IF PATINDEX('%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%', @cleaned) > 0
BEGIN
RETURN @input; -- skip replacement
END
-- Attempt match on 499
DECLARE @start INT = PATINDEX('%499%****%', @output);
IF @start > 0
BEGIN
DECLARE @length INT = CHARINDEX('****', @output, @start) + 3 - @start + 1;
RETURN STUFF(@output, @start, @length, '$');
END
-- Attempt match on 540
SET @start = PATINDEX('%540%****%', @output);
IF @start > 0
BEGIN
SET @length = CHARINDEX('****', @output, @start) + 3 - @start + 1;
RETURN STUFF(@output, @start, @length, '$');
END
-- Attempt match on 543
SET @start = PATINDEX('%543%****%', @output);
IF @start > 0
BEGIN
SET @length = CHARINDEX('****', @output, @start) + 3 - @start + 1;
RETURN STUFF(@output, @start, @length, '$');
END
RETURN @output;
END;
GO
CREATE FUNCTION dbo.MaskCreditCardLike (
@input NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @output NVARCHAR(MAX) = @input;
DECLARE @normalized NVARCHAR(MAX);
-- Clean input to only digits
SET @normalized = REPLACE(@input, '-', '');
SET @normalized = REPLACE(@normalized, '/', '');
SET @normalized = REPLACE(@normalized, '*', '');
SET @normalized = REPLACE(@normalized, 'X', '');
SET @normalized = REPLACE(@normalized, 'x', '');
SET @normalized = REPLACE(@normalized, ' ', '');
-- If no 16-digit sequence exists, return original
IF PATINDEX('%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%', @normalized) = 0
RETURN @input;
-- Naive scan for the 16-digit run in original
DECLARE @i INT = 1;
DECLARE @countDigits INT = 0;
DECLARE @startPos INT = 0;
WHILE @i <= LEN(@input)
BEGIN
IF SUBSTRING(@input, @i, 1) LIKE '[0-9]'
BEGIN
SET @countDigits = @countDigits + 1;
IF @countDigits = 16
BEGIN
SET @startPos = @i - 15;
-- Replace the range with a $
SET @output = STUFF(@output, @startPos, 16, '$');
RETURN @output;
END
END
ELSE IF CHARINDEX(SUBSTRING(@input, @i, 1), '-/*Xx ') > 0
BEGIN
-- allow formatting chars
CONTINUE;
END
ELSE
BEGIN
-- reset if invalid char
SET @countDigits = 0;
END
SET @i = @i + 1;
END
RETURN @output;
END;
GO