Mask the last 4 digit in an account number

Question

I need to do an Select on a table that has many records to mask the Account Number.

Example AccNumber : 123400012341234

Output result should look like 12340001234XXXX

the last 4 digits should become X's.

I come up with this so far

select SUBSTRING('XXXX', 0, 4 - len(RIGHT(AccNumber, 4))) + RIGHT(AccNumber, 4) from table

Did I miss anything?

Answer 1

SELECT 
    LEFT(AcctNumber, LEN(AcctNumber)-4) + 'XXXX' 
FROM YourTable

Or, if the string length is variable you'll want to avoid it erroring out on short strings:

SELECT 
    CASE WHEN LEN(AcctNumber) > 4
        THEN LEFT(AcctNumber, LEN(AcctNumber)-4) + 'XXXX' 
        ELSE 'XXXX'
    END
FROM YourTable

Alternatively, if you're super-sure the account number is always 15 characters:

SELECT 
    LEFT(AcctNumber, 11) + 'XXXX' 
FROM TABLE