Rails、ActiveRecord、PostgreSQL使用regex在字符串中搜索单词

I have a rails 4.1.x application using PostgreSQL as the db.

我有一个rails 4.1。使用PostgreSQL作为db的x应用程序。

I am needing to query a column in an attempt to do an incase sensitive match on a part of a string, while searching with the boundaries of a word. The regex should be inline with /keyword\b/i so using %keyword% would not cut it. I understand that postgres supports regex, however I am not able to get it working correctly. I currently have the following:

我需要查询一个列，以尝试在字符串的一部分上执行incase敏感匹配，同时使用单词的边界进行搜索。regex应该与/关键字\b/i内联，因此使用%关键字%将不会删除它。我理解postgres支持regex，但是我无法使它正常工作。我目前有以下几点:

Page.where("title ~* 'keyword/\b'").all
generages the following:
SELECT "pages".* FROM "pages"  WHERE (title ~* 'keyword')

This currently does not return anything, even records that I know contain the word "keyword", what am I doing wrong? Or rather, what can I do to get my expected result.

这个现在不返回任何东西，甚至我知道的记录包含“关键字”这个词，我做错了什么?或者更确切地说，我能做些什么来得到我期望的结果。

1 个解决方案

#1

Ruby is interpreting \b as a backspace because you're using \b inside a double quoted string. For example, if you look at:

Ruby将\b解释为backspace，因为您在双引号字符串中使用\b。例如，如果你看:

"\b".bytes.to_a

you'll get [8] and that's a backspace character. Then something somewhere interprets that backspace character as a real backspace and you end up with keyword (i.e. keyword/ with the final slash backspaced away) inside the database.

你会得到[8]，那是一个退格字符。然后在某个地方，某个东西将backspace字符解释为一个真正的backspace，然后在数据库中以关键字(即关键字/最后的斜杠后距)结束。

You don't want to say /\b, you want to say \\b to get a single backslash followed by a b down into the database. But that still won't work because \b will be interpreted as a backspace in a PostgreSQL regex:

你不想说/\b，你想说\b让一个反斜杠后面跟着一个b进入数据库。但这仍然行不通，因为在PostgreSQL regex中，\b将被解释为一个后备空间:

Table 9-15. Regular Expression Character-entry Escapes
[...]
\b backspace, as in C

表9 - 15。正则表达式字符-输入转义[…[英语泛读教程\b backspace，如C语言

If you to match at the end of a word, then you want \M:

如果你想在一个单词的结尾进行匹配，那么你需要:

Table 9-17. Regular Expression Constraint Escapes
[...]
\M matches only at the end of a word

表上行线。正则表达式约束转义[…]只在一个字的末尾匹配

Everyone's regex syntax is a little different and you have to use the right syntax with each engine.

每个人的regex语法都有点不同，您必须对每个引擎使用正确的语法。

I think you're looking for:

我认为你在寻找:

Page.where("title ~* 'keyword\\M'")

#1