SQL MSSQL 正则表达式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29206404/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MSSQL Regular expression
提问by Clare Barrington
I have the following REGEX: ^[-A-Za-z0-9/.]+$
我有以下正则表达式：^[-A-Za-z0-9/.]+$
This currently checks whether the value entered into a textbox matches this. If not, it throws an error.
这当前检查输入到文本框中的值是否与此匹配。如果没有，它会抛出一个错误。
I need to check whether anything has already gone into the database that doesnt match this.
我需要检查是否有任何与此不匹配的内容已进入数据库。
I have tired:
我累了：
SELECT * FROM *table* WHERE ([url] NOT LIKE '^[-A-Za-z0-9/.]+$')
SELECT * FROM *table* WHERE PATINDEX ('^[-A-Za-z0-9/.]+$', [url])
UPDATE
更新
So after a bit of research I've realised I don't think I can use REGEXP.
因此，经过一番研究后，我意识到我认为我不能使用 REGEXP。
I thought I could do something like this? Its not giving me the expected results but its running unlike anything else. Can anyone spot anything wrong with it?
我以为我可以做这样的事情？它没有给我预期的结果，但它的运行不同于其他任何东西。任何人都可以发现它有什么问题吗？
SELECT *,
CASE WHEN [url] LIKE '^[-A-Za-z0-9/.]+$'
THEN 'Match'
ELSE 'No Match'
END Validates
FROM
*table*
回答by Clare Barrington
Thank you all for your help.
谢谢大家的帮助。
This is what I have used in the end:
这是我最后使用的：
SELECT *,
CASE WHEN [url] NOT LIKE '%[^-A-Za-z0-9/.+$]%'
THEN 'Valid'
ELSE 'No valid'
END [Validate]
FROM
*table*
ORDER BY [Validate]
回答by Lucas Trzesniewski
Disclaimer:The original question was about MySQL. The SQL Server answer is below.
免责声明：最初的问题是关于 MySQL。SQL Server 答案如下。
MySQL
MySQL
In MySQL, the regex syntax is the following:
在 MySQL 中，正则表达式语法如下：
SELECT * FROM YourTable WHERE (`url` NOT REGEXP '^[-A-Za-z0-9/.]+$')
Use the REGEXPclause instead of LIKE. The latter is for pattern matching using %and _wildcards.
使用REGEXP子句而不是LIKE. 后者用于使用%和_通配符进行模式匹配。
SQL Server
数据库服务器
Since you made a typo, and you're using SQL Server (not MySQL), you'll have to create a user-defined CLR function to expose regex functionality.
由于您打错了，并且您使用的是 SQL Server（而不是 MySQL），因此您必须创建一个用户定义的 CLR 函数来公开正则表达式功能。
Take a look at this articlefor more details.
看看这篇文章了解更多细节。
回答by Matt
Use REGEXP, not LIKE:
使用REGEXP，而不是LIKE：
SELECT * FROM `table` WHERE ([url] NOT REGEXP '^[-A-Za-z0-9/.]+$')
