SQL IF 语句中的正则表达式

发布于 2024-11-05 17:48:17 字数 626 浏览 1 评论 0原文

我知道我可以创建基本的比较触发器（见下文）

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

我将如何使用仅允许数字的正则表达式？（而不是>9999）（相当于 SELECT 要检查的字符串 REGEXP '^[0-9]+$'）

我尝试过：

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

但我得到

：错误：--> #1064 - 你的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，了解在 'REGEX '^[0-9]+$' 附近使用的正确语法，然后调用失败('医院代码无效');第 5 行 END IF'

原文

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]+
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

 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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

ぶ宁プ宁ぶ 2024-11-12 17:48:17

NOT_REGEXP 函数 - 请参阅文档 http://dev.mysql.com/doc /refman/5.1/en/regexp.html

IF colname NOT_REGEXP '^[0-9]+
 THEN

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

IF colname NOT_REGEXP '^[0-9]+
 THEN

回复收藏 0 原文

公布 2024-11-12 17:48:17

只需更改正则表达式

IF NEW.HospitalID REGEX '^[^0-9]+

说明

第一个脱字符是 HospitalID 的开头。
方括号 [ 表示字符类的开始
第二个脱字符是除 0 到 9 之外的所有字符的否定
结束方括号表示字符类的结束
加号字符表示字符类字符必须为 1或更多
美元字符用于 HospitalID 变量的末尾。

简而言之，这个正则表达式检查 HospitalID 变量，如果发现 HospitalID 变量包含数字以外的字符，则调用失败函数。
 THEN 
  call fail('HOSPITAL CODE INVALID'); 
END IF;

说明

第一个脱字符是 HospitalID 的开头。
方括号 [ 表示字符类的开始
第二个脱字符是除 0 到 9 之外的所有字符的否定
结束方括号表示字符类的结束
加号字符表示字符类字符必须为 1或更多
美元字符用于 HospitalID 变量的末尾。

简而言之，这个正则表达式检查 HospitalID 变量，如果发现 HospitalID 变量包含数字以外的字符，则调用失败函数。

Just change the regular expression

IF NEW.HospitalID REGEX '^[^0-9]+

Explaination

The first caret character is for the start of the HospitalID.
the square bracket [ is for the start of the character class
The second caret character is the nagation of all charecter except zero to 9
the end square bracket is for the end of character class
plus sign character is for that the character class characters must be one or more
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.
 THEN 
  call fail('HOSPITAL CODE INVALID'); 
END IF;

Explaination

The first caret character is for the start of the HospitalID.
the square bracket [ is for the start of the character class
The second caret character is the nagation of all charecter except zero to 9
the end square bracket is for the end of character class
plus sign character is for that the character class characters must be one or more
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.

回复收藏 0 原文