<div>
Quick and dirty but won't work on everything:<br />
case when regexp_like(email_address,<wbr />'^.+@.+\..<wbr />+$') then 'Good' else 'Bad' end
</div>


<div>
There are a LOT of email address regular expression validators out there. &nbsp;Just pick one and use it. &nbsp;You might need to tweak it a little to make the syntax Oracle compliant but most regex's can be used.
</div>


<div>
This one is a lot better than my first one but still won't get all bad data:<br />
case when regexp_like(email_address,<wbr />'^[^@.]+@[<wbr />^@.]+\.[^@<wbr />.]+$') then 'Good' else 'Bad' end
</div>


<div>
how about this one?<br />
case when REGEXP_LIKE (CNT1_EMAIL,'^[A-Za-z0-9._<wbr />%+-]+@[A-Z<wbr />a-z0-9.-]+<wbr />\.[A-Za-z]<wbr />{2,4}$')
</div>


<div>
&gt;&gt;how about this one?<br />
<br />
Does it work for you?<br />
<br />
I saw that one as well but it is OLD. &nbsp;There are so many new top level domains, that regex won't work for them.<br />
<br />
<a href="http://data.iana.org/TLD/tlds-alpha-by-domain.txt" rel="ugc">http://data.iana.org/TLD/tlds-alpha-by-domain.txt</a><br />
<br />
If I created:<br />
slightwv@i_drink.beer<br />
<br />
Would it show 'Good' or 'Bad'?
</div>


<div>
Can't get either of them to work, here is an example<br />
<br />
SELECT <br />
STUDENTS.STUDENT_NUMBER,<br />
STUDENTS.LAST_NAME,<br />
STUDENTS.FIRST_NAME,<br />
STUDENTS.SCHOOLID,<br />
STUDENTS.GRADE_LEVEL,<br />
<br />
case when REGEXP_LIKE (CNT1_EMAIL,'^[^@.]+@[^@.]<wbr />+\.[^@.]+$<wbr />')<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;then ''<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;else CNT1_EMAIL || '&lt;-- invalid_format' end CNT1_EMAIL<br />
<br />
FROM <br />
&quot;PS&quot;.&quot;STUDENTS&quot; &quot;STUDENTS&quot;<br />
LEFT JOIN &quot;PS&quot;.&quot;CONTACTS&quot; &quot;CONTACTS&quot; ON &quot;STUDENTS&quot;.&quot;DCID&quot; = &quot;CONTACTS&quot;.&quot;STUDENTSDCID&quot;<br />
<br />
WHERE <br />
(<br />
REGEXP_LIKE (CNT1_EMAIL,'^[^@.]+@[^@.]<wbr />+\.[^@.]+$<wbr />') AND<br />
(CONTACTS.CNT1_EMAIL IS NOT NULL) and (CONTACTS.CNT1_EMAIL != ' ')<br />
)
</div>


<div>
What isn't working? &nbsp;do you get an error or just no data?<br />
<br />
If you use the regexp_like in the where clause, then everything returned must match the pattern so, you should not get any 'invalid' ones?<br />
<br />
<br />
&gt;&gt;(CONTACTS.CNT1_EMAIL IS NOT NULL) and (CONTACTS.CNT1_EMAIL != ' ')<br />
<br />
Make it one using trim. &nbsp;No need to check for a single space. &nbsp;What if there are two or ten spaces in the field?<br />
(trim(CONTACTS.CNT1_EMAIL)<wbr /> IS NOT NULL)
</div>


<div>
I wasn't getting any data before, that works great. &nbsp;Good point!<br />
The regex statement outputs any addresses with periods before the @ sign though which is unwanted. &nbsp;Can any periods before the @ be ignored?
</div>


<div>
&gt;&gt;Can any periods before the @ be ignored? <br />
<br />
You can include/ignore anything you want.<br />
<br />
As far as periods before the '@', is this not a valid email address:<br />
jane.doe@some.domain.com
</div>


<div>
it's valid<br />
I'm only looking to output invalid addresses though
</div>


<div>
OH, I was confused. &nbsp;I didn't test 'jane.doe@some.domain.com'<wbr /> with my regex.<br />
<br />
I'll see what I can do.
</div>


<div>
this is what I have so far btw<br />
just need to ignore any periods before the @ character :-)<br />
<br />
case when REGEXP_LIKE (CNT1_EMAIL,'^[^@.]+@[^@.]<wbr />+\.[^@.]+$<wbr />') then ''<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;when instr(CONTACTS.CNT1_EMAIL,<wbr />'@')=0 then 'missing_@'<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;when instr(CONTACTS.CNT1_EMAIL,<wbr />'.')=0 then 'missing_period'<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;else CNT1_EMAIL || '&lt;-- invalid_format' end CNT1_EMAIL
</div>


<div>
Taken from various sources:<br />
<br />
<a href="http://emailregex.com/" rel="ugc">http://emailregex.com/</a><br />
<br />

<pre><code class="code-1 nolinks" id="code-20-41490606-1"><span>General Email Regex (RFC 5322 Official Standard)</span>
<span>(?:[a-z0-9!#$%&amp;'*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&amp;'*+/=?^_`{|}~-]+)*|&quot;(?:[\x01-\x08\x0b\x0c\x0e-\x1f\x21\x23-\x5b\x5d-\x7f]|\\[\x01-\x09\x0b\x0c\x0e-\x7f])*&quot;)@(?:(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+[a-z0-9](?:[a-z0-9-]*[a-z0-9])?|\[(?:(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.){3}(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?|[a-z0-9-]*[a-z0-9]:(?:[\x01-\x08\x0b\x0c\x0e-\x1f\x21-\x5a\x53-\x7f]|\\[\x01-\x09\x0b\x0c\x0e-\x7f])+)\])</span>
</code></pre>
<br />

<pre><code class="code-1 nolinks" id="code-20-41490606-2"><span>Oracle PL/SQL Email Regex</span>
<span>SELECT email </span>
<span>FROM table_name</span>
<span>WHERE REGEXP_LIKE (email, '[A-Z0-9._%-]+@[A-Z0-9._%-]+\.[A-Z]{2,4}');</span>
</code></pre>
<br />
Other:<br />
<br />
<a href="http://nerd.net.au/oracle/49-apex-application-express/validation-regex/108-email-regex-validation" rel="ugc">http://nerd.net.au/oracle/49-apex-application-express/validation-regex/108-email-regex-validation</a><br />

<pre><code class="code-1 nolinks" id="code-20-41490606-3"><span>^[a-zA-Z0-9][a-zA-Z0-9\.\-]{1,}@[a-zA-Z0-9]{1}[a-zA-Z0-9\.\-]{1,}\.{1}[a-zA-Z]{2,4}$</span>
</code></pre>
<br />
Basics:<br />
<a href="https://galobalda.wordpress.com/2013/07/10/an-attempt-to-validate-email-addresses-with-a-regular-expression/" rel="ugc">https://galobalda.wordpress.com/2013/07/10/an-attempt-to-validate-email-addresses-with-a-regular-expression/</a><br />
<br />
General (awesome site for testing regular expressions):<br />
<a href="https://regex101.com/" rel="ugc">https://regex101.com/</a>
</div>


<div>
Try this one. &nbsp;Again, far from perfect but I've not really seen a really good one.<br />
<br />
case when regexp_like(email_address,<wbr /> '^[^@]+\.?[^@]*@[^@.]+\.[^<wbr />@.]+$' ) then 'Good' else 'Bad' end
</div>


<div>
Alex,<br />
Most of those aren't valid. &nbsp;They are all old. &nbsp;I didn't test the RFC one but the others are out of date.
</div>


<div>
works well, still looking through all of the data<br />
only found one issue so far<br />
if the address contains more than two periods after the @ then it's included in the results which should not be the case<br />
for example first.last@school1.miami.f<wbr />l.us is valid
</div>


<div>
Fantastic!! :-)<br />
<br />
This is what I have right now, I can't thank you enough for your help!<br />
<br />
case when REGEXP_LIKE (CNT1_EMAIL, '^[^@]+\.?[^@]*@[^@.]+(\.[<wbr />^@.]+)+$' ) then ''<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;when instr(CONTACTS.CNT1_EMAIL,<wbr />'@')=0 then CNT1_EMAIL || ' &lt;-- missing_@'<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;when instr(CONTACTS.CNT1_EMAIL,<wbr />'@')&gt;1 then CNT1_EMAIL || ' &lt;-- multiple_addresses'<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;when instr(CONTACTS.CNT1_EMAIL,<wbr />'.')=0 then CNT1_EMAIL || ' &lt;-- missing_period'<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;else CNT1_EMAIL || ' &lt;-- invalid_format' end CNT1_EMAIL
</div>


<div>
found one last issue<br />
if the address contains multiple periods before the @ then it's output as invalid
</div>


<div>
There will be more, remember, what I posted won't catch everything.<br />
<br />
I'm not going to be able to post something that will catch everything.<br />
<br />
This appears to fix the q..@q.q issue:<br />
regexp_like(email_address,<wbr /> '^([^@.]+\.?)+@[^@.]+(\.[^<wbr />@.]+)+$')
</div>


<div>
Doesn't like that one<br />
never completes and doesn't output any field values
</div>


<div>
I ran it against 11.2.0.2. &nbsp;I can't remember your Oracle version.<br />
<br />
I'll see if I can come up without the new group.
</div>


<div>
Try my stand-alone test case. &nbsp;I just tried it with 12.1.0.2 and it ran fine:<br />

<pre><code class="code-10 nolinks" id="code-20-41490787-1"><span>with mydata as (</span>
<span>select null email_address from dual</span>
<span>union all</span>
<span>select 'q@q@q.q' email_address from dual</span>
<span>union all</span>
<span>select 'q..@q.q' email_address from dual</span>
<span>union all</span>
<span>select 'q.@q.q' email_address from dual</span>
<span>union all</span>
<span>select 'q@q.q' email_address from dual</span>
<span>union all</span>
<span>select 'q.q.q@q.q.q' email_address from dual</span>
<span>union all</span>
<span>select 'q.q@q.q' email_address from dual</span>
<span>union all</span>
<span>select 'q.q.q.q.q@q.q.q.q.q' email_address from dual</span>
<span>union all</span>
<span>select 'q@q..q' email_address from dual</span>
<span>union all</span>
<span>select 'qqq' email_address from dual</span>
<span>)</span>
<span>select email_address,</span>
<span>case when</span>
<span>regexp_like(email_address, '^([^@.]+\.?)+@[^@.]+(\.[^@.]+)+$')</span>
<span>then 'Good' else 'Bad' end</span>
<span>from mydata</span>
<span>/</span>
</code></pre>
</div>


<div>
Can it be modified to only output the invalid values though?
</div>


<div>
Sure.<br />
<br />
Add it to the where clause:<br />
<br />
select ...<br />
where ...<br />
and NOT regexp_like ...
</div>


<div>
Throws an invalid operational error message<br />
where<br />
(CONTACTS.CNT1_EMAIL NOT REGEXP_LIKE '^[^@]+\.?[^@]*@[^@.]+(\.[<wbr />^@.]+)+$' )
</div>


<div>
You need the complete regexp_like function call...<br />
<br />
select ...<br />
from ...<br />
where ...<br />
and NOT REGEXP_LIKE (CNT1_EMAIL, '^[^@]+\.?[^@]*@[^@.]+(\.[<wbr />^@.]+)+$' )
</div>


<div>
If I do that then it gives me all the null fields from the &lt;-- missing_@ statement<br />
even with an is not null in the where statement
</div>


<div>
&gt;&gt;If I do that then it gives me all the null fields from <br />
<br />
I didn't say it was the ONLY thing in the where clause, if you don't want nulls, add to the where clause.<br />
<br />
select ...<br />
from ...<br />
where ...<br />
and NOT REGEXP_LIKE (CNT1_EMAIL, '^[^@]+\.?[^@]*@[^@.]+(\.[<wbr />^@.]+)+$' ) <br />
and trim(CNT1_EMAIL) is not null<br />
...
</div>


<div>
<div class="content wysiwyg-content">
Can someone tell me how a column could be checked to verify that it's contents which &quot;should&quot; be email addresses contain the @ character and at least one period? &nbsp;Thanks!!
</div>
</div>


Can someone tell me how a column could be checked to verify that it's contents which "should" be email addresses contain the @ character and at least one period?  Thanks!!

Verifying email value syntax

Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.

Oracle Database

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.