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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
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
1 2 3 4 |
SELECT CreditCardID , dbo.udf_CreditCardNumberMasked(CreditCardNumber, 4, 0) FROM PL.dbo.CreditCards; |
Test the function. Show the last 4 digits and add spaces
1 2 3 4 |
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
1 2 3 4 |
SELECT CreditCardID , dbo.udf_CreditCardNumberMasked(CreditCardNumber, 7, 1) AS CreditCardNumberMasked FROM PL.dbo.CreditCards; |
Keep it simple :-)