regex - ORacle SQL Regular Expressions -

i'm looking create regular expressions code testing. in below have created constraint check 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 better way have constraint check when new user creating account?

also, i'm trying search user name of 'luke haire' below sql queries returns no results:

 select * customers regexp_like (name, '^([l(u|i|o)ke]+)[\][haire]$'); 

i don't understand first question. check constraint best way have check constraints. why databases support it. there worse ways, such triggers, typically best way using built-in, standard capability.

your specific constraint seems constrained. email addresses commonly contain "." , "-" well.

as second question, problem suffix. try:

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

i should add prefer above equivalent:

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

the issue difference between like , regexp. like matches entire string. so, under circumstances, prefer have regular expressions emulate explicitly having beginning , end. avoid confusion. use simpler searches , regular expressions more complex ones.

however, still expect no matches because \ not allowed character before @, according check constraint.