Using column data as pattern for regexp match

Question

Recently, we have changed from MySQL to PostgreSQL. Most of the queries have been translated except for the ones with the Mysql 'REGEXP' keyword:

MySQL (prepared statement):
SELECT * FROM table WHERE ? REGEXP identifier;

We have a 'table', and one of the columns is called 'identifier'. This 'identifier' column contains the actual regular expression pattern.

So, instead of 'hard-coding' the regexp pattern in the query, it looks-up the identifier column for the pattern.

In Postgresql, we need to use the '~' keyword instead of the 'REGEXP' one (which is MySQL only), but with Postgresql I cannot seem to extract the pattern from a column.

I've tried following queries without success:

SELECT * FROM table WHERE 'test' ~ "identifier";
ERROR:  invalid regular expression: parentheses () not balanced


SELECT * FROM table WHERE "identifier" ~ 'test';
-> no results

for testing purposes I created a number of records where the "identifier" column contains '.*' as value (regular expression for match everything), but still I do not get the appropriate result.

Help is very much welcome, thank you!

Why are you using column name in quotation marks? Try without them. — sufleR
– sufleR, Commented Nov 26, 2012 at 12:40
@sufleR It isn't the problem. Double quotes are used in postgresql to specify exact name of column/table/schema/function... — Ihor Romanchenko
– Ihor Romanchenko, Commented Nov 26, 2012 at 12:46
We need to see your actual regexp pattern. Please provide sample rows that fail for you in the question or as sqlfiddle. — Erwin Brandstetter
– Erwin Brandstetter, Commented Nov 26, 2012 at 19:16

Ihor Romanchenko · Accepted Answer · 2012-11-26 12:44:31Z

3

Maybe the problem is in one of the regexp strings? (As the error string says)

I've tested

SELECT *
FROM table1 t
WHERE 'cat' ~ t.reg;

And it works for me. Here is my SQL Fiddle. http://sqlfiddle.com/#!12/f245c/3

The problem can be in different regexp interpretation. Details about postgresql regexp here http://www.postgresql.org/docs/current/static/functions-matching.html

UPD Also read http://www.postgresql.org/docs/current/interactive/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS for detail about postgresql strings. You need this to write regexp correctly.

edited Nov 26, 2012 at 12:44

answered Nov 26, 2012 at 12:38

Ihor Romanchenko

29k9 gold badges56 silver badges45 bronze badges

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

4 Comments

Freqo Over a year ago

Thanks Igor, I've read the postgresql regexp page. the actual regexp pattern should be correct (I use .* to test). Can I ask which version of postgresql you are using? because I tested your example and it didn't work for me: SELECT * FROM "Table1" t WHERE 'cat' ~ t.reg; ERROR: invalid regular expression: parentheses () not balanced

Ihor Romanchenko Over a year ago

@Freqo I tested for 8.3 8.4 9.1 9.2 . See the SQL Fiddle link sqlfiddle.com/#!1/f245c/1 (You can choose postgresql version at the top of the page)

mu is too short Over a year ago

@Freqo: Can you reproduce your problem on sqlfiddle.com so that we can see exactly what is going on?

Freqo Over a year ago

No, I cannot reproduce with sqlfiddle.com, it works over there ;)

Collectives™ on Stack Overflow

Using column data as pattern for regexp match

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related