1

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]$');
LivinLife
  • 109
  • 1
  • 13
  • See [Using a regular expression to validate an email address](http://stackoverflow.com/q/201323/1509264). – MT0 Jan 16 '16 at 22:48
  • 1
    Your regex misses [lots of TLDs](http://data.iana.org/TLD/tlds-alpha-by-domain.txt) including all the country code TLDs and will not validate any address with multiple sub-domains (i.e. `jsmith@x.y.z.co.uk`). 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.with+symbol@example.com`. – MT0 Jan 16 '16 at 23:00

2 Answers2

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 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! – LivinLife Jan 16 '16 at 21:00
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].*$')
hayama600
  • 13
  • 4
  • Thanks for your comments and helped tremendously. I was able to use the select statement with the regexp_like. – LivinLife Jan 16 '16 at 21:00