Re: check constraint

[Kareem Sedki]

Possibly the best way to validate string inputs, to my knowledge, is regular expressions.

mysql> select 'string to check' [NOT] REGEXP 'regular expression';

If there was a match in the string a value of 1 would be returned, 0 otherwise.

For digits:

select '1111111' regexp '^([0-9]+)$'; -- would return 1
select ' 1111111' regexp '^([0-9]+)$'; -- would return 0
select '1111111 ' regexp '^([0-9]+)$'; -- would return 0
select '111 1111' regexp '^([0-9]+)$'; -- would return 0

For (printing ) characters:

select 'a' regexp '^([alpha]+)$'; -- would return 1
select ' a' regexp '^([alpha]+)$'; -- would return 0
select 'a ' regexp '^([alpha]+)$'; -- would return 0
select ' ' regexp '^([alpha]+)$'; -- would return 0
select '1' regexp '^([alpha]+)$'; -- would return 0


If you are afraid there might be leading or trailing spaces, then

SET @input = LTRIM(RTRIM(@input));

Check section number 11.4.2 of the MySQL 5 manual, it is the section for regular expressions. If you use another version, find out what section it is :D.

You might use a BEFORE INSERT trigger on the table you want use this check with. The trigger should check for either of the conditions, i.e., characters only OR digits. Functions and procedures are also valid options, but it depends on what you want do. Maybe the best way is a BEFORE INSERT trigger.

Hope this helps.

Kareem