'Mask the last 4 digit in an account number

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?

sql-servertsql


Solution 1:[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

Solution 2:[2]

You could use STUFF for this also. It was designed for this exact type of thing. I am using the excellent sample data posted by Sami. There should be no difference in performance but it is a little bit fewer keystrokes. :) The other advantage is it won't crash if the account code passed in is too short. It will just return NULL.

declare @MyStrings TABLE (
       STR VARCHAR(200)
    );

INSERT INTO @MyStrings VALUES
('123400012341234'),
('123400012340000004321'),
('12340102');


select stuff(STR, len(STR) - 3, 4, 'xxxx')
from @MyStrings

Solution 3:[3]

Here is an example:

CREATE TABLE MyStrings (
       STR VARCHAR(200)
    );

INSERT INTO MyStrings VALUES
('123400012341234'),
('123400012340000004321'),
('12340102'),
('1');

SELECT CASE WHEN LEN(STR) >= 8 THEN SUBSTRING(STR, 1, LEN(STR)-4)+'XXXX' END AS Result
FROM MyStrings;

Results:

+-----------------------+
|        Result         |
+-----------------------+
| 12340001234XXXX       |
| 12340001234000000XXXX |
| 1234XXXX              |
| NULL                  |
+-----------------------+

Solution 4:[4]

Try using the stuff function it's much easier:

 Select 
 AcctNumber=STUFF ( AcctNumber, (LEN(AcctNumber)-3), 4 , 'XXXX' )
 from Table1

hope it helps you :)

Solution 5:[5]

I used this query on ORACLE SQL to find the expected output;

create table acc_study(acc_no int not null); insert into acc_study values(123400012341234);

select length(Acc_no),rpad(rpad(acc_no,11,''),15,'') as output from acc_study;

output result-12340001234XXXX

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Russell Fox
Solution 2 Sean Lange
Solution 3
Solution 4 Mohamed Azizi
Solution 5 Tejaswini

Related Questions