Need to mask encrypted field in SQL

Question

I want to mask some SQL data by #######.

I am trying following SQL:

SELECT 
    CONCAT ( SUBSTRING(email_address,1,3), 
     '#####' ,
     SUBSTRING(email_address,LENGTH(email_address) - 2, LENGTH(email_address) )
    ) AS Result, REPLACE( SUBSTRING(email_address,1,3, ' ', ' ' ) as Replaced
FROM employees

email_address column in table are already encrypted or hashed. I want to mask data dynamically ( according to length ) I want to show only 5 character as it is in the middle and remaining all in '#########' format. Length should not be changed after masking.

Data existing in table like:

15a97dbe57dc993847fbf18394948b8b
1so8GhueT58ked3OvsMeqHbQfIN+zyg8gGj6sQrUB6A=

after you converted the data, you may just select left(columnNameAfterdecrypted, 5) + '#######', would that a better idea? — ah_hau, Sep 03 '14 at 06:25
Hey, but after selecting left it will give only left data. I want whole string with some masked character, original string length should not be changed. — Kunfu Panda, Sep 03 '14 at 08:08
input: 15a97dbe57dc993847fbf18394948b8b Output: 15a97#####################48b8b — Kunfu Panda, Sep 03 '14 at 08:12

ah_hau · Accepted Answer · 2014-09-03T08:51:36.757

SELECT 
    LEFT('15a97dbe57dc993847fbf18394948b8b',5)
     + REPLICATE('#', LEN('15a97dbe57dc993847fbf18394948b8b') - 10)
     + RIGHT('15a97dbe57dc993847fbf18394948b8b', 5)

This should get what you want(MSSQL)

sql fiddle

Edit 1 (PostGreSQL)

SELECT CONCAT ( LEFT( bank_account_number_encrypted,5 ), 
REPEAT('#', LENGTH(bank_account_number_encrypted) - 10) , 
RIGHT(bank_account_number_encrypted, 5) ) 
FROM employees

Need to mask encrypted field in SQL

1 Answers1