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
toZ
anda
toz
;- digits
0
to9
;- 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 tojohn.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
throughz
(in a case-insensitive manner), the digits0
through9
, 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:
SUBSTR(t.email, 1, INSTR(t.email, '@')-1) AS part1
- split email (@
is delimiter)regexp_replace(SUBSTR(t.email, 1, INSTR(t.email, '@')-1), '[[:alnum:]]', '*') AS part1_masked
- replace all alfanumeric characters with*
INSTR(part1_masked, '*',1, 5) > 0
- check if exists at least 5*
in masked stringConcatenate 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
Delete ... from ... Where ... In
Sql Server Select Datetime Without Seconds
Sql Azure Backup & Restore Strategy
Standard SQL Alternative to Oracle Decode
Postgresql Batch Insert or Ignore
Sql Distance Query Without Trigonometry
Creating a Db Table Null Best Practices
How to Get Current/Todays Date Data in SQL Server
How to Check If a Directory Exists Using SQL Server
Does Sqlite Support Replication
Does SQL Server Support Is Distinct from Clause
T-Sql: Comparing Two Tables - Records That Don't Exist in Second Table
Find The Sids of The Suppliers Who Supply Every Part
Trying to Connect to an Odbc Server Using Rodbc in Ubuntu