Rails, ActiveRecord, PostgreSQL search for word within string using regex
postgresql, rails-activerecord, regex, ruby-on-rails, string
Solution
Ruby is interpreting `\b` as a backspace because you're using `\b` inside a double quoted string. For example, if you look at:
"\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.
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:
Table 9-15. Regular Expression Character-entry Escapes [...] `\b` backspace, as in 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.
I think you're looking for:
Page.where("title ~* 'keyword\\M'")
Problem
I have a rails 4.1.x application using PostgreSQL as the db. 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: ``` 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.