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

Create test table

Create the function

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

Test udf_CreditCardNumberMasked - last 4 digits, no spaces

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

Test udf_CreditCardNumberMasked - last 4 digits, add spaces

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

Test udf_CreditCardNumberMasked - last 7 digits, add spaces

Keep it simple :-)

Share

Leave a comment

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