Sqlite - Limit Characters for different parts of one String with Check

Question

I have to make sure that any inserted Email has the format: "[email protected]"

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 '%_@%_._%'));

• Akina: Mail GLOB '[A-Za-z0-9]+@[A-Za-z0-9]+\.[A-Za-z]+'?
• CL.: This can be done with a single GLOB.

Answer 1

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 '%_@%_.%_')