SQL匹配特殊字符正则表达式 [英] SQL Match Special Characters Regexp

问题描述

我正在寻找一条SQL语句，该语句仅返回表中Name字段包含特殊字符(不包括下划线)的行.

I'm looking for an SQL statement that will return only rows of my table whose Name field contains special characters (excluding underscores).

我尝试过:

SELECT * FROM 'table' WHERE Name REGEXP '^[!#$%&()*+,\-./:;<=>?@[\\\]^`{|}~]+$'

但是没有骰子，它将返回一个空结果集(尽管我专门添加了包含%, $, and #个字符的Name字段的行).

But no dice, this returns an empty result set (despite there being rows I specifically added with Name fields containing %, $, and # characters).

推荐答案

第一个问题似乎是^和$符号(迈克C总结的速度比我为什么要快...)

The first problem seems to be is the ^ and $ signs (Mike C summarized it quicker than I did why...)

但是我也看到了转义的问题:表示正则表达式中某些内容的所有特殊字符都应专门放在[]中的转义，因此[，]，^，-

But I see escaping problems too: all special characters that mean something in regexp should be escaped specially placed in the [], so [, ], ^, -

这是关于如何在字符组内转义特殊字符的问题在MySQL正则表达式中.

regex文档中详细介绍的结论:

Conclusion detailed in the regex documentation:

括号表达式是用[[]]括起来的字符的列表.通常，它与列表中的任何单个字符匹配(但请参见下文).

A bracket expression is a list of characters enclosed in '[]'. It normally matches any single character from the list (but see below).

• 如果列表以'^'开头，则它匹配任何单个字符(但请参见 下方)，而不是列表的其余部分.

• If the list begins with '^', it matches any single character (but see below) not from the rest of the list.

如果列表中的两个字符用'-'分隔，则这是整理顺序中这两个字符(包括两个字符)之间的完整字符范围的简写形式. ASCII中的[0-9]与任何十进制数字匹配.

If two characters in the list are separated by '-', this is shorthand for the full range of characters between those two (inclusive) in the collating sequence, e.g. '[0-9]' in ASCII matches any decimal digit.

两个范围共享一个端点是非法的(！)，例如'高手'.范围非常依赖于序列，并且可移植程序应避免依赖它们.

It is illegal(!) for two ranges to share an endpoint, e.g. 'a-c-e'. Ranges are very collating sequence-dependent, and portable programs should avoid relying on them.

要在列表中包含文字']'，请将其设为第一个字符(可能在'^'之后).

To include a literal ']' in the list, make it the first character (following a possible '^').

要包含文字'-'，请将其设为第一个或最后一个字符，或范围的第二个端点.

To include a literal '-', make it the first orlast character, or the second endpoint of a range.

要将文字-"用作范围的第一个端点，请将其括在"[."中和'.]'使其成为整理元素(请参见下文).

To use a literal '-' as the first endpoint of a range, enclose it in '[.' and '.]' to make it a collating element (see below).

除了这些以及使用'['的某些组合(请参阅以下段落)之外，所有其他特殊字符(包括'\')在 方括号表达式.

With the exception of these and some combinations using '[' (see next paragraphs), all other special characters, including '\', lose their special significance within a bracket expression.

编辑 这里是一个SQL小提琴，涉及一些有关]字符的有趣正则表达式

EDIT Here is an SQL fiddle about some interesting regexes regarding the ] character

DDL: 创建表txt( txt varchar(200) );

DDL: create table txt ( txt varchar(200) );

insert into txt values ('ab[]cde');
insert into txt values ('ab[cde');
insert into txt values ('ab]cde');
insert into txt values ('ab[]]]]cde');
insert into txt values ('ab[[[[]cde');
insert into txt values ('ab\\]]]]cde');
insert into txt values ('ab[wut?wut?]cde');

查询:

幼稚的方法来匹配一组[和]字符.从语法上讲可以，但是该组是单个[字符，并且此后匹配多个]字符.

Naive approach to match a group of [ and ] chars. Syntactically OK, but the group is the single [ char, and it matches multiple ] chars afterwards.

SELECT * FROM txt WHERE txt 
REGEXP 'ab[[]]+cde';

转义->相同???

SELECT * FROM txt WHERE txt 
REGEXP 'ab[[\]]+cde';

双重转义->不起作用，组现在是[和\

Double escape -> doesn't work, group is now a [ and a \

SELECT * FROM txt WHERE txt 
REGEXP 'ab[[\\]]+cde';

将封闭支架与组内的一个开放支架交换.这是我写过的最奇怪的正则表达式-至此...

Swapping the closing bracket with the opening one inside the group. This is the weirdest regex I ever wrote - to this point...

SELECT * FROM txt WHERE txt 
REGEXP 'ab[][]+cde';

我认为在奇怪的噩梦中，我将被这种(完全有效的)正则表达式杀死，

I will get killed by such a (totally valid!) regex in a weird nightmare, I think:

SELECT * FROM txt WHERE txt 
REGEXP 'ab[]wut?[]+cde';

这篇关于SQL匹配特殊字符正则表达式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！