I am trying to create a column constraint in Apache Derby to check that the input data is in the form of three uppercase alpha characters plus a three digit number e.g. ABC100 or XYZ999.
I have the following, which works in MS Server
CHECK ((ASCII(SUBSTRING(consultant_id,1,1)))BETWEEN 65 AND 90 AND
(ASCII(SUBSTRING(consultant_id,2,1)))BETWEEN 65 AND 90 AND
(ASCII(SUBSTRING(consultant_id,3,1)))BETWEEN 65 AND 90 AND
(CAST(SUBSTRING(consultant_id,4,3)as smallint)BETWEEN 100 AND 999))
however, Apache Derby does not seem to recognise the ASCII keyword.
I know I could do
CHECK (SUBSTR(consultant_id,1,1)) IN ('A','B','C'......................'Z') AND..............
but that seems very clumsy.
Can someone point me in the direction of a more elegant solution please