Rails, ActiveRecord, PostgreSQL search for word within string using regex

Question

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.

mu is too short · Accepted Answer · 2014-11-20 06:49:56Z

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'")

Collectives™ on Stack Overflow

Rails, ActiveRecord, PostgreSQL search for word within string using regex

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related