Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Invalid Character in Email Address Check Query [SOLVED]

user16854May 16 2008 — edited May 19 2008

I have been asked to identify a list of all email addresses on our system which contain invalid characters.

Acceptable characters are: 1-9, and a-z, and also '\','.','@'.

Is this something a regular expression would be able to handle?

I have made a start via:

SELECT fu.user_id
     , fu.user_name
     , fu.email_address fu_email
  FROM applsys.fnd_user fu
 WHERE (
           fu.email_address LIKE '%(%'
        OR fu.email_address LIKE '%)%'
        OR fu.email_address LIKE '%,%'
        OR fu.email_address LIKE '% %'
        OR fu.email_address LIKE '%+%'
        OR fu.email_address NOT LIKE '%@%'
        OR fu.email_address NOT LIKE '%.%'
       );

But that seems pretty clunky, because I will have to add a new line for every character I want to search for. I have seen regular expressions being used for similar in the past, but I cannot tailor regexpressions to suit my need, because I find them really confusing.

Thanks

Message was edited by:
jimr

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 16 2008
Added on May 16 2008
3 comments
3,450 views