0

I have to make sure that any inserted Email has the format: "X@Y.Z"

X and Y may only contain letters and numbers and Z can only have Letters.

I tried something like this, but i cant figure out how to limit the Characters for X,Y and Z separately.

Create Table User(
Mail varchar check(Mail NOT GLOB '*[^A-Za-z0-9@.]*' AND Mail LIKE '%_@%_._%'));
2
  • Mail GLOB '[A-Za-z0-9]+@[A-Za-z0-9]+\.[A-Za-z]+'? – Akina Feb 19 '20 at 8:56
  • This can be done with a single GLOB. – CL. Feb 19 '20 at 9:39
0

So i figured out how to do exactly what i wanted to do. I used substrings and limited the characters for each individual part of the EMail.

CREATE TABLE User(
EMail varchar NOT NULL COLLATE NOCASE check( (substr(EMail, INSTR(EMail,'.')+1) NOT GLOB '*[^A-Za-z]*') 
AND (substr(EMail,1, INSTR(EMail,'@')-1) NOT GLOB '*[^A-Za-z0-9]*')
AND (substr(EMail, INSTR(EMail,'@')+1, ((INSTR(EMail,'.')-1) - (INSTR(EMail,'@')+1))+1 ) NOT GLOB '*[^A-Za-z0-9]*') 
AND EMAIL LIKE '%_@%_.%_')

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.