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

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('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

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;

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;

Keep it simple :-)