is this postgres regex expression equivalent to this like expression?

Question

This query returns no rows :

SELECT * FROM t WHERE t.s ~ '^(N_|B_).*'

while this one does

SELECT * FROM t WHERE t.s like 'N_%' or t.s like 'B_%';

Why are they not equivalent ?

klin · Accepted Answer · 2017-02-14 23:49:10Z

1

An underscore character has special meaning in LIKE, for the documentation:

An underscore (_) in pattern stands for (matches) any single character; a percent sign (%) matches any sequence of zero or more characters. (...) To match a literal underscore or percent sign without matching other characters, the respective character in pattern must be preceded by the escape character.

So these queries are equivalent:

SELECT * FROM t WHERE t.s ~ '^(N_|B_).*';
SELECT * FROM t WHERE t.s like 'N\_%' or t.s like 'B\_%';

answered Feb 14, 2017 at 23:49

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

is this postgres regex expression equivalent to this like expression?

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