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

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

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

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;

Test udf_CreditCardNumberMasked - last 4 digits, no spaces

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;

Test udf_CreditCardNumberMasked - last 4 digits, add spaces

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;

Test udf_CreditCardNumberMasked - last 7 digits, add spaces

Keep it simple :-)

Leave a comment

Your email address will not be published. Required fields are marked *