Rails, ActiveRecord, PostgreSQL search word in string using regex
I have a rails 4.1.x application using PostgreSQL as db.
I need to query a column trying to perform a disassociated match on a portion of a string when searching with word boundaries. The regex must be inline with / keyword \ b / i, so using% keyword% will not abbreviate it. I understand that postgres supports regex, however I cannot get it to work correctly. I currently have the following:
Page.where("title ~* 'keyword/\b'").all
generages the following:
SELECT "pages".* FROM "pages" WHERE (title ~* 'keyword')
This currently does not return anything, even entries that I know contain the word "keyword", what am I doing wrong? Or rather, what can I do to get the expected result.
source to share
Ruby interprets it \b
as backspace because you are using \b
double quoted string inside. For example, if you look at:
"\b".bytes.to_a
you get [8]
a backspace character as well. Then something, somewhere, interprets that backspace character as a real backspace and you end up with keyword
(i.e., keyword/
with a trailing slash) inside the database.
You don't want to say /\b
, you want to say \\b
to get one backslash and then b
down to the database. But that still won't work, because it \b
will be interpreted as backspace in PostgreSQL regex :
Table 9-15. Symbolic expressions with regex
[...]\b
backspace as in C
If you want a match at the end of a word, then you want\M
:
Table 9-17. Conditional Expression Constraints Constraints
[...]\M
matches only at the end of a word
Each regex syntax is slightly different and you need to use the correct syntax with each engine.
I think what you are looking for is:
Page.where("title ~* 'keyword\\M'")
source to share