Postgresql and ActiveRecord where: Regex matching

Your LIKE query:

full_name ilike '%(first|last)%(last|first)%'

won't work because LIKE doesn'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).

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:

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:

full_name ~* '(first|last)\s+(last|first)'

That translates to this bit of AR:

Person.where('full_name ~* :pat', :pat => '(first|last)\s+(last|first)')# or thisPerson.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_sql calls and you might see something interesting:

> puts Person.where('full_name ~* :pat', :pat => 'a\s+b').to_sqlSELECT "people".* FROM "people"  WHERE (full_name ~* 'a\s+b')> puts Person.where('full_name ~* :pat', :pat => "a\s+b").to_sqlSELECT "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