SELECT values by another table with predefined regular expression

Question

I would like to select rows in my table by my another table with my predefined regular expressions.
Below you can see my example tables:

Table code

id   |  codename
-----+------------
1    |   123456
2    |   NF123
3    |   AAA444
4    |   EEE123
5    |   EEE423
6    |   AB123E

Table validcode

 id   |  validreg
------+-----------
 1    |   [E]{3}[0-9]{3}
 2    |   NF123

And here is the test case on sqlfidlle.

This is one of the my select statements I tried, but it doesn't work:

SELECT n.codename FROM code n
WHERE EXISTS (SELECT * FROM validcode WHERE n.codename ~* E'^' || validreg || '$');

I get this error:

ERROR: argument of WHERE must be type boolean, not type text:
SELECT codename FROM code n WHERE EXISTS (SELECT * FROM validcode WHERE n.codename ~* E'^' || validreg || '$');

Is possible to do this somehow?

You don't need where exists or subquery. You should be fine just by joining the queries, something like SELECT n.codename FROM code n, validcode WHERE n.codename ~* E'^' || validcode.validreg || '$'. Probably not very efficient, though. — Sami Kuhmonen
– Sami Kuhmonen, Commented May 17, 2014 at 20:59
But still it doesn't work, i think the problem is in the string where i do the concatenating somewhere here ~* E'^' || validcode.validreg || '$' — Bushwacka
– Bushwacka, Commented May 17, 2014 at 21:03

Erwin Brandstetter · Accepted Answer · 2014-05-17 23:26:58Z

2

SELECT n.codename FROM code n, validcode
WHERE  n.codename ~* ('^' || validcode.validreg || '$')

Braces because otherwise the parser thinks that n.codename ~* E'^' is one part and to that concatenates the rest.

Also your original query would work with the additional braces.

edited May 17, 2014 at 23:26

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

answered May 17, 2014 at 21:10

Sami Kuhmonen

31.4k9 gold badges67 silver badges81 bronze badges

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

1 Comment

Erwin Brandstetter Over a year ago

The problem is operator precedence which is fixed properly in this answer. The original WHERE EXISTS (...) would be just as efficient as an implicit or explicit join.

Collectives™ on Stack Overflow

SELECT values by another table with predefined regular expression

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related