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.
Advertisement
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