0

With the example below, I only got TRUE with the expression SIMILAR TO; LIKE and ~ both showed FALSE (failed to work here I guess?)

As SIMILAR TO is not favorable in many SO posts, I'd like to learn if there's a way doing regex match with alternatives by using LIKE or ~.

SELECT 'thomas' SIMILAR TO '%(h|x)%'
-- result: t

SELECT 'thomas' LIKE '%(h|x)%'
-- result: f

SELECT 'thomas' ~* '%(h|x)%'
-- result: f
uniquegino
  • 1,841
  • 1
  • 12
  • 11
  • 1
    `LIKE` does not support regular expressions. Where in [the manual](https://www.postgresql.org/docs/current/static/functions-matching.html#functions-like) did you get that impression? –  Oct 19 '17 at 18:42
  • *Never* use `SIMILAR TO`: https://stackoverflow.com/a/12459689/939860 – Erwin Brandstetter Oct 25 '17 at 01:20

2 Answers2

5

LIKE supports pattern matching using _ for any single character and % for any sequence of characters so this:

SELECT 'thomas' LIKE '%(h|x)%'

doesn't work because LIKE doesn't understand (...) for grouping or | for alternation, those are just literal characters in a LIKE pattern.

SIMILAR TO supports _ and % the same as LIKE but adds grouping with (...), alternation with |, and a few other things so this:

SELECT 'thomas' SIMILAR TO '%(h|x)%'

works as expected.

~* uses POSIX regexes so (...) is for grouping and | is for alternation but % is just a percent sign; that means that this:

SELECT 'thomas' ~* '%(h|x)%'

is looking for an h or x surrounded by percent signs and doesn't work they way you expect it to.

Your ~* version will work if you use a proper regex like:

SELECT 'thomas' ~* '(h|x)' -- alternation
SELECT 'thomas' ~* 'h|x'   -- alternation without an unnecessary grouping
SELECT 'thomas' ~* '[hx]'  -- or a character class

The documentation linked to above covers all of this.

mu is too short
  • 426,620
  • 70
  • 833
  • 800
0

You can use ~* in this case:

SELECT 'thomas' ~* '.*(h|x).*'

or

SELECT 'thomas' ~* 'h|x'

Keep in mind that you should use POSIX regex syntax with ~, ~* operators.

mingca
  • 542
  • 4
  • 12