Postgresql 和 ActiveRecord 其中：正则表达式匹配
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22799631/
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
Postgresql and ActiveRecord where: Regex matching
提问by Patrick
I created this regex in normal Regex
我在普通的正则表达式中创建了这个正则表达式
/(first|last)\s(last|first)/i
It matches the first three of
它匹配前三个
first last
Last first
First Last
First name
I am trying to get all the records where the full_namematches with the regex I wrote. I'm using PostgreSQL
我正在尝试获取full_name与我编写的正则表达式匹配的所有记录。我正在使用 PostgreSQL
Person.where("full_name ILIKE ?", "%(first|last)%(last|first)%")
This is my attempt. I also tried SIMILAR TOand ~with no luck
这是我的尝试。我也尝试过SIMILAR TO和~没有运气
回答by mu is too short
Your LIKE query:
您的 LIKE 查询：
full_name ilike '%(first|last)%(last|first)%'
won't work because LIKEdoesn't understand regex grouping ((...)) or alternation (|), LIKE only understands _for a single character (like .in a regex) and %for any sequence of zero or more characters (like .*in a regex).
将不起作用，因为LIKE不理解正则表达式分组 ( (...)) 或交替 ( |)，LIKE 仅理解_单个字符（如.在正则表达式中）和%任何零个或多个字符序列（如.*在正则表达式中）。
If you hand that pattern to SIMILAR TO then you'll find 'first last'but none of the others due to case problems; however, this:
如果您将该模式交给 SIMILAR TO，那么'first last'由于案例问题，您会发现但没有其他任何模式；然而，这：
lower(full_name) similar to '%(first|last)%(last|first)%'
will take care of the case problems and find the same ones as your regex.
将处理案例问题并找到与您的正则表达式相同的问题。
If you want to use a regex (which you probably do because LIKE is very limited and cumbersome and SIMILAR TO is, well, a strange product of the fevered minds of some SQL standards subcommittee) then you'll want to use the case-insensitive matching operator and your original regex:
如果您想使用正则表达式（您可能这样做是因为 LIKE 非常有限且繁琐，而 SIMILAR TO 是某些 SQL 标准小组委员会狂热思想的奇怪产品），那么您将需要使用不区分大小写的匹配运算符和您的原始正则表达式：
full_name ~* '(first|last)\s+(last|first)'
That translates to this bit of AR:
这转化为这一点 AR：
Person.where('full_name ~* :pat', :pat => '(first|last)\s+(last|first)')
# or this
Person.where('full_name ~* ?', '(first|last)\s+(last|first)')
There's a subtle change in my code that you need to take note of: I'm using single quotes for my Ruby strings, you're using double quotes. Backslashes mean more in double quoted strings than they do in single quoted strings so '\s'and "\s"are different things. Toss in a couple to_sqlcalls and you might see something interesting:
您需要注意我的代码中有一个细微的变化：我的 Ruby 字符串使用单引号，而您使用的是双引号。反斜线意味着更多的在双引号字符串中比他们在单引号字符串，这样做的'\s'和"\s"是不同的东西。折腾几个to_sql电话，你可能会看到一些有趣的事情：
> puts Person.where('full_name ~* :pat', :pat => 'a\s+b').to_sql
SELECT "people".* FROM "people" WHERE (full_name ~* 'a\s+b')
> puts Person.where('full_name ~* :pat', :pat => "a\s+b").to_sql
SELECT "people".* FROM "people" WHERE (full_name ~* 'a +b')
That difference probably isn't causing you any problems but you need to be very careful with your strings when everyone wants to use the same escape character. Personally, I use single quoted strings unless I specifically need the extra escapes and string interpolation functionality of double quoted strings.
这种差异可能不会给您带来任何问题，但是当每个人都想使用相同的转义字符时，您需要非常小心您的字符串。就个人而言，我使用单引号字符串，除非我特别需要双引号字符串的额外转义和字符串插值功能。
Some demos: http://sqlfiddle.com/#!15/99a2c/6
