CODEWITHSUNDEEP
sqlsql-server
Sugandha Sharma
Sugandha Sharma

Reputation: 65

REPLACE and SUBSTRING in SQL

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.

Upvotes: 0

Views: 1475

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

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.

Upvotes: 0

Albert Alberto
Albert Alberto

Reputation: 952

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'

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520908

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';

Demo

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.

Upvotes: 1

Related Questions