I'm trying to write a check constraint to validate email addresses that may include an apostrophe in the email address. Such as joe.o'malley@navy.mil. Here is my sample setup:
create table emails
( email_address varchar2(150)
)
/
insert into emails values('joe.black.ctr@us.army.mil') ;
insert into emails values('mary.anne.lucas@dfas.mil') ;
insert into emails values('joey.o''halleran@mail.mil') ;
commit;
sql> select * from emails;
EMAIL_ADDRESS
-------------------------------------------------
joe.black.ctr@us.army.mil
mary.anne.lucas@dfas.mil
joey.o'halleran@mail.mil
alter table emails add constraint email_address_format_ck
CHECK ( REGEXP_LIKE ( email_address, '^[a-z0-9._%-]\'?+@[a-z0-9._%-]+\.mil$','c'));
ERROR at line 2:
ORA-00911: invalid character
It doesn't like *\'?*
My understanding is this means one or more single-quotes. Anyone know the correct syntax to accept apostrophes?