T-SQL: UDF Scalar Function to mask Credit Card numbers

We usually mask the credit card number in reports or end user data with asterisk (*). Here I show a scalar-valued function that masks the Credit Card Number.

The length of the credit card number in most of the situations is 16 digits. For easy reading, the digits are split by blocks of 4 digits: 1234 1234 1234 1234.

The function accepts 3 parameters:

@CreditCardNumber – the credit card number to be masked
, @NumOfShownChars – number of the last X digits to be shown
, @IsSeparated – adds spaces between each 4 digits

Create database

USE [master];
GO

IF (DB_ID('PL') IS NOT NULL)
	BEGIN
		ALTER DATABASE PL SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
		DROP DATABASE PL;
	END
GO

CREATE DATABASE PL;
GO

USE PL;
GO

USE [master];

IF (DB_ID('PL') IS NOT NULL)

BEGIN

ALTER DATABASE PL SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

DROP DATABASE PL;

END

CREATE DATABASE PL;

USE PL;

Create test table

IF (OBJECT_ID('PL.dbo.CreditCards', 'U') IS NOT NULL)
	BEGIN DROP TABLE PL.dbo.CreditCards; END
GO

CREATE TABLE PL.dbo.CreditCards
	(
		CreditCardID INT IDENTITY(1, 1)
		, CreditCardNumber CHAR(16)
		, CONSTRAINT PK_CreditCards
			PRIMARY KEY (CreditCardID)
	);
GO

INSERT PL.dbo.CreditCards (CreditCardNumber)
SELECT '1234567812345678'
UNION ALL SELECT '1212121212121212'
UNION ALL SELECT '1234123412341234'
UNION ALL SELECT '1234567890123456'
UNION ALL SELECT NULL;
GO

SELECT *
FROM PL.dbo.CreditCards;
GO

IF (OBJECT_ID('PL.dbo.CreditCards', 'U') IS NOT NULL)

BEGIN DROP TABLE PL.dbo.CreditCards; END

CREATE TABLE PL.dbo.CreditCards

(

CreditCardID INT IDENTITY(1, 1)

, CreditCardNumber CHAR(16)

, CONSTRAINT PK_CreditCards

PRIMARY KEY (CreditCardID)

);

INSERT PL.dbo.CreditCards (CreditCardNumber)

SELECT '1234567812345678'

UNION ALL SELECT '1212121212121212'

UNION ALL SELECT '1234123412341234'

UNION ALL SELECT '1234567890123456'

UNION ALL SELECT NULL;

SELECT *

FROM PL.dbo.CreditCards;

Create the function

IF (OBJECT_ID('dbo.udf_CreditCardNumberMasked', 'FN') IS NOT NULL)
	BEGIN DROP FUNCTION dbo.udf_CreditCardNumberMasked; END
GO

CREATE FUNCTION dbo.udf_CreditCardNumberMasked
	(
		@CreditCardNumber VARCHAR(19)
		, @NumOfShownDigits TINYINT
		, @IsSeparated BIT
	)
RETURNS VARCHAR(19)
AS
BEGIN
	DECLARE
		@ReturnCreditCardNumber VARCHAR(19)
		, @NumOfMaskedDigits TINYINT;

	SELECT @NumOfMaskedDigits = (LEN(@CreditCardNumber) - @NumOfShownDigits);


	SELECT @ReturnCreditCardNumber = STUFF (@CreditCardNumber, 1, @NumOfMaskedDigits, REPLICATE('*', (@NumOfMaskedDigits)));

	IF (@IsSeparated = 1)
	BEGIN
		SELECT @ReturnCreditCardNumber = STUFF(STUFF(STUFF(@ReturnCreditCardNumber, 5, 0, ' '), 10, 0, ' '), 15, 0, ' ' );
	END

	RETURN @ReturnCreditCardNumber;
END
GO

IF (OBJECT_ID('dbo.udf_CreditCardNumberMasked', 'FN') IS NOT NULL)

BEGIN DROP FUNCTION dbo.udf_CreditCardNumberMasked; END

CREATE FUNCTION dbo.udf_CreditCardNumberMasked

(

@CreditCardNumber VARCHAR(19)

, @NumOfShownDigits TINYINT

, @IsSeparated BIT

)

RETURNS VARCHAR(19)

BEGIN

DECLARE

@ReturnCreditCardNumber VARCHAR(19)

, @NumOfMaskedDigits TINYINT;

SELECT @NumOfMaskedDigits = (LEN(@CreditCardNumber) - @NumOfShownDigits);

SELECT @ReturnCreditCardNumber = STUFF (@CreditCardNumber, 1, @NumOfMaskedDigits, REPLICATE('*', (@NumOfMaskedDigits)));

IF (@IsSeparated = 1)

BEGIN

SELECT @ReturnCreditCardNumber = STUFF(STUFF(STUFF(@ReturnCreditCardNumber, 5, 0, ' '), 10, 0, ' '), 15, 0, ' ' );

END

RETURN @ReturnCreditCardNumber;

END

Test the function. Show the last 4 digits and don’t add spaces

SELECT
	CreditCardID
	, dbo.udf_CreditCardNumberMasked(CreditCardNumber, 4, 0)
FROM PL.dbo.CreditCards;

SELECT

CreditCardID

, dbo.udf_CreditCardNumberMasked(CreditCardNumber, 4, 0)

FROM PL.dbo.CreditCards;

Test the function. Show the last 4 digits and add spaces

SELECT
	CreditCardID
	, dbo.udf_CreditCardNumberMasked(CreditCardNumber, 4, 1) AS CreditCardNumberMasked
FROM PL.dbo.CreditCards;

SELECT

CreditCardID

, dbo.udf_CreditCardNumberMasked(CreditCardNumber, 4, 1) AS CreditCardNumberMasked

FROM PL.dbo.CreditCards;

Test the function. Show the last 7 digits and add spaces

SELECT
	CreditCardID
	, dbo.udf_CreditCardNumberMasked(CreditCardNumber, 7, 1) AS CreditCardNumberMasked
FROM PL.dbo.CreditCards;

SELECT

CreditCardID

, dbo.udf_CreditCardNumberMasked(CreditCardNumber, 7, 1) AS CreditCardNumberMasked

FROM PL.dbo.CreditCards;

Keep it simple :-)