Replacing Certain Character in Email Addresses with '*' in an SQL Query

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

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

SELECT LEFT(@String, 3) + '*****@'
+ REVERSE(LEFT(RIGHT(REVERSE(@String) , CHARINDEX('@', @String) +2), 2))
+ '******'
+ RIGHT(@String, 4)

Result

exa*****@gm******.com

Just thought of another simpler solution

SELECT LEFT(@String, 3) + '*****@' 
+ SUBSTRING(@String, CHARINDEX('@',@String)+1,2)
+ '*******'
+ RIGHT(@String, 4)

This will also give you the exact same results.

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

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

<?php
$email = 'youremail@ddre.ss';

$beforeAt=explode("@",$email);
$beforeAt=$beforeAt[0];
$asterisks=str_repeat("*",strlen($beforeAt)-2);
$beforeAt=$beforeAt{0}.$asterisks.substr($beforeAt,-1);
$domain=explode("@",$email);
$domain=explode(".",$domain[1]);
$extension=$domain[count($domain)-1];
$domain=implode(".",$domain);
$domain=substr($domain,0,-1*strlen($extension)-1);
$asterisks=str_repeat("*",strlen($domain)-1);
$email=$beforeAt."@".substr($domain,0,1).$asterisks.".".$extension;

echo $email;
?>

Replace all email addresses after @ in Sql

I tried this and it worked perfectly.

 UPDATE myTable  SET UserEMail = 
(SELECT SUBSTRING(UserEMail, 0, PATINDEX('%@%',UserEMail)) + '@example.org'
from myTable U WHERE U.UserID = myTable.UserID)

Hide characters in email address using an SQL query

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

SQL SELECT query not working with Email Addresses

Are you sure that it's not working. See a proof here that it works http://sqlfiddle.com/#!9/26b00/4. But you should change your queries a bit as shown below

SELECT * FROM table1 WHERE `Email` = 'something@gmail.com' -- No need of LIKE operator

SELECT * FROM table1 WHERE `Email` = 'something@gmail.com' -- No need of LIKE operator

SELECT * FROM table1 WHERE `Email` LIKE '%@gmail.com' -- search before string

SELECT * FROM table1 WHERE `Email` LIKE 'something_gmail.com' -- search a single char

EDIT:

Per your latest comment your collation armscii8_general_ci is the issue here. For example create the table like

CREATE TABLE Table1
(`email` varchar(19) collate armscii8_general_ci)
;

INSERT INTO Table1
(`email`)
VALUES
('something@gmail.com')
;

Do a select * ... returns below; as you can see the . as turned to © kind of copyright symbol and that's why the wildcard with LIKE operator not working.

something@gmail©com

Change your query to use _ wilcard with LIKE operator to match any single character and it will work fine. See http://sqlfiddle.com/#!9/ec46f/8

SELECT * FROM Table1 WHERE `Email` LIKE 'something@gmail_com'; 

Replace all email addresses after '@' in PostgresSQL

You can do it with the REGEXP_REPLACE function.

If you need to select only, you can use the following query:

SELECT REGEXP_REPLACE(email, '@.*', '@test.com')
FROM tab

If instead you want to update the field value, you can use:

UPDATE tab
SET email = REGEXP_REPLACE(email, '@.*', '@test.com');

Try it here.

Ruby - Match And Replace Only Specific Characters In Email

You may use

s = s.gsub(/(?:\G(?!\A)|\A[^@]{2})\K[^@]/, '*')

Or,

s = s.gsub(/(?<=[^@]{2})[^@](?=[^@]*@)/, '*')

The regexps match

  • (?:\G(?!\A)|\A[^@]{2}) - either the end of the previous successful match or start of string and any 2 chars other than a @
  • \K - match reset operator that discards the text matched so far
  • [^@] - a char other than @.

The second regex matches

  • (?<=[^@]{2}) - a location immediately preceded with any two chars other than a @
  • [^@] - a char other than a @
  • (?=[^@]*@) - a location immediately followed with any 0 or more chars other than a @ and then a @.

What characters are allowed in an email address?

See RFC 5322: Internet Message Format and, to a lesser extent, RFC 5321: Simple Mail Transfer Protocol.

RFC 822 also covers email addresses, but it deals mostly with its structure:

 addr-spec   =  local-part "@" domain        ; global address     
local-part = word *("." word) ; uninterpreted
; case-preserved

domain = sub-domain *("." sub-domain)
sub-domain = domain-ref / domain-literal
domain-ref = atom ; symbolic reference

And as usual, Wikipedia has a decent article on email addresses:

The local-part of the email address may use any of these ASCII characters:

  • uppercase and lowercase Latin letters A to Z and a to z;
  • digits 0 to 9;
  • special characters !#$%&'*+-/=?^_`{|}~;
  • dot ., provided that it is not the first or last character unless quoted, and provided also that it does not appear consecutively unless quoted (e.g. John..Doe@example.com is not allowed but "John..Doe"@example.com is allowed);
  • space and "(),:;<>@[\] characters are allowed with restrictions (they are only allowed inside a quoted string, as described in the paragraph below, and in addition, a backslash or double-quote must be preceded by a backslash);
  • comments are allowed with parentheses at either end of the local-part; e.g. john.smith(comment)@example.com and (comment)john.smith@example.com are both equivalent to john.smith@example.com.

In addition to ASCII characters, as of 2012 you can use international characters above U+007F, encoded as UTF-8 as described in the RFC 6532 spec and explained on Wikipedia. Note that as of 2019, these standards are still marked as Proposed, but are being rolled out slowly. The changes in this spec essentially added international characters as valid alphanumeric characters (atext) without affecting the rules on allowed & restricted special characters like !# and @:.

For validation, see Using a regular expression to validate an email address.

The domain part is defined as follows:

The Internet standards (Request for Comments) for protocols mandate that component hostname labels may contain only the ASCII letters a through z (in a case-insensitive manner), the digits 0 through 9, and the hyphen (-). The original specification of hostnames in RFC 952, mandated that labels could not start with a digit or with a hyphen, and must not end with a hyphen. However, a subsequent specification (RFC 1123) permitted hostname labels to start with digits. No other symbols, punctuation characters, or blank spaces are permitted.

How to mask a email id without masking its domain name and the dots in oracle SQL?

You should search for something like Oracle Data Masking and Subsetting.


Using only text operations:

WITH test_data AS 
(
SELECT 'alex.hales@gmail.com' AS email FROM dual
UNION ALL
SELECT 'Joeroot.eng@yahoo.co.in' FROM dual
UNION ALL
SELECT 't.e.s.t.ex.ampl.e@do.ma.i.com' FROM dual
UNION ALL
SELECT 't1@z.a' FROM dual
)
SELECT z.email,
CASE WHEN INSTR(part1_masked, '*',1, 5) > 0
THEN SUBSTR(part1_masked,1,INSTR(part1_masked, '*',1, 5))
|| SUBSTR(part1, INSTR(part1_masked, '*',1, 5)+1)
ELSE part1_masked
END || '@'
|| SUBSTR(part2_masked,1,INSTR(part2_masked, '.',-1)-1)
|| SUBSTR(part2,INSTR(part2, '.',-1,1)) AS masked_email
FROM
(
SELECT
email
,SUBSTR(t.email, 1, INSTR(t.email, '@')-1) AS part1
,SUBSTR(t.email,INSTR(t.email, '@')+1) AS part2
,regexp_replace(SUBSTR(t.email, 1, INSTR(t.email, '@')-1),
'[[:alnum:]]', '*') AS part1_masked
,regexp_replace(SUBSTR(t.email,INSTR(t.email, '@')+1),
'[[:alnum:]]', '*') AS part2_masked
FROM test_data t
) z

LiveDemo

Output:

╔═══════════════════════════════╦═══════════════════════════════╗
║ EMAIL ║ MASKED_EMAIL ║
╠═══════════════════════════════╬═══════════════════════════════╣
║ alex.hales@gmail.com ║ ****.*ales@*****.com ║
║ Joeroot.eng@yahoo.co.in ║ *****ot.eng@*****.**.in ║
║ t.e.s.t.ex.ampl.e@do.ma.i.com ║ *.*.*.*.*x.ampl.e@**.**.*.com ║
║ t1@z.a ║ **@*.* ║
╚═══════════════════════════════╩═══════════════════════════════╝

How it works:

  1. SUBSTR(t.email, 1, INSTR(t.email, '@')-1) AS part1 - split email (@ is delimiter)

  2. regexp_replace(SUBSTR(t.email, 1, INSTR(t.email, '@')-1), '[[:alnum:]]', '*') AS part1_masked - replace all alfanumeric characters with *

  3. INSTR(part1_masked, '*',1, 5) > 0 - check if exists at least 5 * in masked string

  4. Concatenate part up to 5 stars from masked string and the rest from unmasked

EDIT:

WITH test_data AS 
(
SELECT 'alex.hales@gmail.com' AS email FROM dual
UNION ALL
SELECT 'Joeroot.eng@yahoo.co.in' FROM dual
UNION ALL
SELECT 't.e.s.t.ex.ampl.e@do.ma.i.com' FROM dual
UNION ALL
SELECT 't1@z.a' FROM dual
)
SELECT z.email,
CASE WHEN INSTR(part1, '.')-1 < 0 THEN part1_masked
WHEN INSTR(part1, '.')-1 <=5
THEN SUBSTR(part1_masked,1, INSTR(part1, '.'))
|| SUBSTR(part1, INSTR(part1, '.')+1)
ELSE SUBSTR(part1_masked, 1, 5) || SUBSTR(part1, 6)
END
|| '@'
|| SUBSTR(part2_masked,1,INSTR(part2_masked, '.',-1)-1)
|| SUBSTR(part2,INSTR(part2, '.',-1,1)) AS masked_email
FROM
(
SELECT
email
,SUBSTR(t.email, 1, INSTR(t.email, '@')-1) AS part1
,SUBSTR(t.email,INSTR(t.email, '@')+1) AS part2
,regexp_replace(SUBSTR(t.email, 1, INSTR(t.email, '@')-1),
'[[:alnum:]]', '*') AS part1_masked
,regexp_replace(SUBSTR(t.email,INSTR(t.email, '@')+1),
'[[:alnum:]]', '*') AS part2_masked
FROM test_data t
) z

LiveDemo2

Ouptut:

╔═══════════════════════════════╦═══════════════════════════════╗
║ EMAIL ║ MASKED_EMAIL ║
╠═══════════════════════════════╬═══════════════════════════════╣
║ alex.hales@gmail.com ║ ****.hales@*****.com ║
║ Joeroot.eng@yahoo.co.in ║ *****ot.eng@*****.**.in ║
║ t.e.s.t.ex.ampl.e@do.ma.i.com ║ *.e.s.t.ex.ampl.e@**.**.*.com ║
║ t1@z.a ║ **@*.a ║
╚═══════════════════════════════╩═══════════════════════════════╝


Related Topics



Leave a reply



Submit