Home » SQL & PL/SQL » SQL & PL/SQL » Using REGEXP_LIKE (Oracle 12c)
Using REGEXP_LIKE [message #662795] Fri, 12 May 2017 05:15 Go to next message
GusGF
Messages: 10
Registered: July 2015
Location: UK
Junior Member
I'm trying to setup a check constraint to accept email addresses.

So the rule I want to set is to allow this ...
FirstnameLastname@gmail.com
but not this...
Firstname.Lastname@gmail.com
I want the first and last names to not be split by a period (no '.' appearing before the '@'). I've tried various solutions to no avail and below was my starting point.


drop table email;

create table email(
email_address varchar2(50),
constraint email_chk check(regexp_like(email_address, '[[:alpha:]]+@[[:alpha:]]+\.(com|net)')));

insert into email values('test.test@gmail.net');
Re: Using REGEXP_LIKE [message #662796 is a reply to message #662795] Fri, 12 May 2017 05:48 Go to previous message
flyboy
Messages: 1856
Registered: November 2006
Senior Member
If you want the regular expression to match the whole string, use the start (^) and end ($) position metacharacters (http://en.wikipedia.org/wiki/Regular_expression#POSIX_basic_and_extended) in the mask.
with x as ( select column_value email_address
            from table(sys.odcivarchar2list('test.test@gmail.net'
                                          , 'testtest@gmail.net')) )
select email_address,
       case when regexp_like(email_address, '^[[:alpha:]]+@[[:alpha:]]+\.(com|net)$')
            then 1
            else 0
       end match_pattern
from x;
Without them, REGEXP_LIKE is satisfied with only the substring matching ('test@gmail.net').

[Edit: English spelling]

[Updated on: Fri, 12 May 2017 05:49]

Report message to a moderator

Previous Topic: Loop SP out cursor inside a stored procedure
Next Topic: Pass two or more value
Goto Forum:
  


Current Time: Thu Oct 18 13:11:59 CDT 2018