ORacle SQL Regular Expressions

Question

I'm looking to create some regular expressions code for some testing. In the below I have created a Constraint Check for an email address.

     create table testemail (username varchar2(50) NOT NULL,
     Password varchar(15) Not NULL,
     CONSTRAINT pk_usertest PRIMARY KEY (username),
     CONSTRAINT un_emailtest CHECK (REGEXP_LIKE(username,'^([[:alnum:]]+)@[[:alnum:]]+.(com|net|org|edu|gov|mil)$'))

Is there a better way to have a constraint check when a new user creating an account?

Also, I'm trying to search a user by the name of 'Luke Haire' but my below sql queries returns no results:

 select * from customers where regexp_like (name, '^([L(u|i|o)ke]+)[\][Haire]$');

See Using a regular expression to validate an email address. — MT0
– MT0, Commented Jan 16, 2016 at 22:48
Your regex misses lots of TLDs including all the country code TLDs and will not validate any address with multiple sub-domains (i.e. [email protected]). It also won't accept an IP address in the domain (i.e. jsmith@[192.168.2.1] or jsmith@[IPv6:2001:db8::1]). In the local part it won't accept valid e-mail addresses like [email protected]. — MT0
– MT0, Commented Jan 16, 2016 at 23:00

Gordon Linoff · Accepted Answer · 2016-01-16 21:13:07Z

2

I don't really understand your first question. A check constraint is the best way to have a check constraints. That is why databases support it. There are worse ways, such as triggers, but typically the best way is using this built-in, standard capability.

Your specific constraint seems too constrained. Email addresses commonly contain "." and "-" as well.

As for the second question, the problem is the suffix. So try:

select *
from customers
where regexp_like(name, '^([L(u|i|o)ke]+)[\][Haire]@.*$');
---------------------------------------------------^

I should add that I prefer the above to the equivalent:

where regexp_like(name, '^([L(u|i|o)ke]+)[\][Haire]@');

The issue is the difference between like and regexp. like always matches the entire string. So, under most circumstances, I prefer to have regular expressions emulate this by explicitly having the beginning and end. This is to avoid confusion. I use like for simpler searches and regular expressions for more complex ones.

However, I would still expect this to get no matches because \ is not an allowed character before the @, according to the check constraint.

edited Jan 16, 2016 at 21:13

answered Jan 16, 2016 at 16:19

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

LivinLife Over a year ago

Thanks for your comments and helped tremendously. Good point about how email addresses contain - and . as well. What are your thoughts for having a simple check constraint that would only check having a valid email address that will check letters, numbers, special characters before the @ and .com or similar? Thanks much!

hayama600 · Accepted Answer · 2016-01-16 17:15:15Z

1

Your query doesn't return any rows because you have two mistakes in your pattern.

Second expected character, according to your pattern is \ so in character list [\] you should add space [\ ] or [\[:space:]]. You are missing quantifier for [Haire] character list. At the moment your pattern works like:

regexp_like ('Luke Haire', '^([L(u|i|o)ke]+)[\](H|a|i|r|e)$')

One possible pattern for your entry string is:

 regexp_like (name, '^([L(u|i|o)ke]+)[\ ][Haire].*$')

answered Jan 16, 2016 at 17:15

hayama600

134 bronze badges

1 Comment

LivinLife Over a year ago

Thanks for your comments and helped tremendously. I was able to use the select statement with the regexp_like.

Collectives™ on Stack Overflow

ORacle SQL Regular Expressions

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related