Bujji Bujji - 1 year ago 60
SQL Question

Replacing certain characters in email addresses with '*' in an SQL query

I am trying to implement similar feature in my website and wanted to display email like below .

enter image description here

Below is the MYSQL code I have written and it is working OK for the scenarios I tested . But wanted to check with you guys if we have any better solution for this .

SELECT CONCAT(RPAD(CONCAT(RPAD(firstletter, firstpartlength - 2, '*'),
beforedomain), (
secondpartlength - Length(afterdot) +
firstpartlength ),
'*'), afterdot) hiddenemail
FROM (SELECT SUBSTRING(email, 1, 1) firstletter,
SUBSTRING(email, INSTR(email, '@') - 1, 3) beforedomain,
SUBSTR(SUBSTRING(email, INSTR(email, '@')),
INSTR(email, '@')), '.'))
LENGTH(SUBSTRING(email, 1, INSTR(email, '@') - 1))
LENGTH(SUBSTRING(email, INSTR(email, '@') + 1))
FROM test_table) a

Thanks for your help


Answer Source

As stated in my comment, I would recommend a PHP-based solution like this:

$email = 'youremail@ddre.ss';


echo $email;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download