4

I know I can create basic comparison triggers (see below)

CREATE TRIGGER HospitalCheck
BEFORE INSERT ON Hospital
FOR EACH ROW
 BEGIN
  IF NEW.HospitalID > 9999 THEN
     call fail('HOSPITAL CODE INVALID');
  END IF;    
END

How would I go about using a regular expression that only allowed numbers? (instead of the >9999) (the equivalent of SELECT string to check REGEXP '^[0-9]+$')

I tried:

IF NEW.HospitalID REGEX '^[0-9]+$' THEN 
  call fail('HOSPITAL CODE INVALID'); 
END IF;

But i get

: ERROR : --> #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'REGEX '^[0-9]+$' THEN call fail('HOSPITAL CODE INVALID'); END IF' at line 5

Jocelyn
  • 11,209
  • 10
  • 43
  • 60
Mitch
  • 41
  • 1
  • 1
  • 2
  • Yes, but I'm getting a syntax error – Mitch May 08 '11 at 08:29
  • `IF NEW.HospitalID REGEX '^[0-9]+$' THEN` `call fail('HOSPITAL CODE INVALID');` `END IF;` `END ` : ERROR : --> #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'REGEX '^[0-9]+$' THEN call fail('HOSPITAL CODE INVALID'); END IF' at line 5 – Mitch May 08 '11 at 08:32
  • 1
    That regex will mark it invalid if it IS numeric. Either replace REGEX (should be REGEXP) with NOT_REGEXP, or use '^[^0-9]+$' – David Fells May 08 '11 at 08:36
  • on a side note, i hope you don't make plan on using this in a `CHECK` statement... xD – Sebas Apr 24 '13 at 23:30

3 Answers3

1

NOT_REGEXP function - see the docs http://dev.mysql.com/doc/refman/5.1/en/regexp.html

IF colname NOT_REGEXP '^[0-9]+$' THEN
David Fells
  • 6,678
  • 1
  • 22
  • 34
  • Apologies if I was unclear, I was asking how I would replace IF NEW.HospitalID > 9999 THEN <-- from the code above with a REGEXP equivalent that will only allow numbers – Mitch May 08 '11 at 08:28
  • That's why I pointed you to the function. It would benefit you to implement the function from the documentation rather than get someone to write it for you :) – David Fells May 08 '11 at 08:33
1

Just change the regular expression

IF NEW.HospitalID REGEX '^[^0-9]+$' THEN 
  call fail('HOSPITAL CODE INVALID'); 
END IF;

Explaination

  1. The first caret character is for the start of the HospitalID.
  2. the square bracket [ is for the start of the character class
  3. The second caret character is the nagation of all charecter except zero to 9
  4. the end square bracket is for the end of character class
  5. plus sign character is for that the character class characters must be one or more
  6. the dollar character is for the end of the HospitalID variable.

So in short this regular expression checks the HospitalID variable and if it found that the HospitalID variable had other than numeric characters it call fail function.

Adeel Raza Azeemi
  • 571
  • 5
  • 16
0

IF NOT column_name REGEXP '^[0-9]+$' THEN

Prabhat Pal
  • 25
  • 6