email validation in pl/sql

i know that pl/sql's strong point is not regular expressions, and i'm well aware of better solutions to do this.

given my requirements per employer, email validation will need to be done in the pl/sql layer. i'd like to implement RFC822/RFC1035 for email validation and was wondering if anyone had some code that does this?

what is pl/sql's? If by 'pl' you mean PERL, then you are mistaken - regular expressions and pattern matching are exactly what the language is meant for.

As for a PCRE to match am email address, you'd better search the forums or do a google. This is perhaps the most common implementation of regular expressions in the history of mankind.

christo

not perl. pl/sql.

i posted this in the oracle forum because pl/sql is specific to oracle.

my bad - over to the 'pl/sql' experts

Don't know of any reg expression code available in pl/sql. How about if you wrote a java function that could be called by the pl/sql?

If you happen to be running Oracle Web Apps, you could use owa_pattern, for regex matching. Or use javascript within the pl/sql block. I haven't done this myself, though the javascript option is in heavy use at my office.

You can find the documentation on owa_pattern at
Oracle's doc site

Hope this helps a bit,
Marty

I don't know how strong your validation needs to be, but you can probably easily use substr() and a For loop to check that there are:
-some numbers/letters/periods
-followed by one @ symbol
-followed by some more numbers/letters
-followed by a period and a valid extension, such as .net, .org, .com, .co.uk, etc.

Basically figure out what your validation rules are, and then use substr to break the varchar2 into pieces, then check the pieces.


And for those who don't know, here's some background info courtesy of Orafaq.com:
What is PL/SQL and what is it used for?
PL/SQL is Oracle's Procedural Language extension to SQL. PL/SQL's language syntax, structure and data types are similar to that of ADA. The PL/SQL language includes object oriented programming techniques such as encapsulation, function overloading, information hiding (all but inheritance). PL/SQL is commonly used to write data-centric programs to manipulate data in an Oracle database.
(http://www.orafaq.com/faqplsql.htm)