Matt Matt - 4 months ago 8
SQL Question

Hide characters in email address using an SQL query

I have a very specific business requirement to hide certain characters within an email address when returned from SQL and I have hit the limit of my ability with SQL to achieve this. I was wondering if someone out there would be able to point me in the right direction. Essentially, my business is asking for the following:

test@email.com
to become
t*\*t@e**l.com

or

thislong@emailaddress.com
to become
t******h@e**********s.com


I am aware that if either portion of the email before of after the @ are less than 3 characters, then this won't work, but I intend on checking for this and dealing with it appropriately. I have tried a mixture of SUBSTRING, STUFF, LEFT/RIGHT etc but I can't quite get it right.

Answer

Very interesting and very much tough to generate generic solution try this

this may help you

DECLARE @String VARCHAR(100) = 'sample@gmail.com'

SELECT STUFF(STUFF(@STring,
                   CHARINDEX('@',@String)+2,
                   (CHARINDEX('.',@String, CHARINDEX('@',@String))-CHARINDEX('@',@String)-3),
                   REPLICATE('*',CHARINDEX('.',@String, CHARINDEX('@',@String))-CHARINDEX('@',@String)))
               ,2
               ,CHARINDEX('@',@String)-3
               ,REPLICATE('*',CHARINDEX('@',@String)-3))

OUTPUT will be

s****e@g******l.com

Similar way for thislong@emailaddress.com

OUTPUT will be

t******g@e*************s.com