I am trying to mask a certain string such that only its last 4 characters are visible and rest are masked.
I have tried the below code in SQL server.
select REPLACE(trans_no,substring(trans_no,1,len(trans_no)-4),'*') from uvi_generalledgerwf where trans_no = '11043770'
Actual Result - *3770
Whereas, my expected outcome should be ****3770
This transaction number's length may vary but the last 4 digits from right should always be visible.
Here is a solution for SQL Server, which however should work across pretty much any database:
SELECT
trans_no,
SUBSTRING('**********', 1, LEN(trans_no) - 4) +
SUBSTRING(trans_no, LEN(trans_no) - 3, 4) AS trans_no_masked
FROM uvi_generalledgerwf
WHERE
trans_no = '11043770';
The strategy here is to simply concatenate the correct number of * from a static string along with the last for digits from the trans_no. To support trans_no of a higher length, use a larger string literal with enough * in it.
This solution works when the trans_no is not of a fixed length.
DECLARE @trans_no CHAR ( 255 );
SET @trans_no = '0808683370';
SELECT
CONCAT(
REPLICATE
( '*', LEN( @trans_no ) - 4 ),
SUBSTRING( @trans_no, CASE WHEN LEN( @trans_no ) > 3 THEN LEN( @trans_no ) - 3 ELSE 1 END, LEN(@trans_no) )
)
You can test it here https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=974fe247ef4a9ea2ded5e84abb74d0ff
Then in your real application, you can use it as follows:
SELECT
CONCAT(
REPLICATE ( '*', LEN ( trans_no ) - 4 ),
SUBSTRING( trans_no, CASE WHEN LEN ( trans_no ) > 3 THEN LEN ( trans_no ) - 3 ELSE 1 END, LEN ( trans_no ) )
) as masked_trx_no
FROM
uvi_generalledgerwf
WHERE
trans_no = '11043770'
I think the simplest method is:
select concat(replicate('*', len(trans_no) - 4), right(trans_no, 4))
from uvi_generalledgerwf
where trans_no = '11043770';
The key idea here is that replicate() returns NULL when the value is negative -- so there is no problem. concat() ignores NULL values so short strings are handled correctly.
Here is a db<>fiddle showing how it works for various string lengths.
