-----Original Message----- From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Gauthier, Dave Sent: Friday, November 18, 2011 3:37 PM To: John R Pierce; PostgreSQL Subject: Re: How to use like with a list The example was a general case. It won't be jo and mo and fo. In fact, the values will be stored in a csv perl scalar. If you know perl... $str = "jo,mo,do,fo"; Using DBI, I need to "prepare" a query that will accept a string like the one above. So... select name,age,weight from people_table where name ~ '^(' || replace(?, ',', '|') || ')l' but it doesn't work :-( bi_dev=# create table test (name text); CREATE TABLE bi_dev=# insert into test (name) values ('jolly'),('frenchie'),('dollymadison'); INSERT 0 3 bi_dev=# select name from test where name ~ '^(' || replace('jo,mo,do,fo', ',', '|') || ')l'; ERROR: argument of WHERE must be type boolean, not type text -----Original Message----- From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of John R Pierce Sent: Friday, November 18, 2011 3:23 PM To: PostgreSQL Subject: Re: How to use like with a list On 11/18/11 12:18 PM, Richard Broersma wrote: > On Fri, Nov 18, 2011 at 12:13 PM, John R Pierce<pierce@xxxxxxxxxxxx> wrote: > >> > where field ~ '^(jo|mo|do|fo)' > Don't forget to add the l as the end: > > where field ~ '^(jo|mo|do|fo)l' ah, yeah, that. and to complete the original requirement... where field ~ '^(' || replace(?, ',', '|') || ')l' btw, no need for a .* on the end, since the regex isn't anchored at the end with a $ -- john r pierce N 37, W 122 santa cruz ca mid-left coast -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general ---------------------------------------------------- It looks as if the WHERE clause is resulting in: WHERE ( ( name ~ '^(' ) || replace ... ) But you want: WHERE ( name ~ ( '^(' || replace ... ) ) Add parentheses to make explicit what you want to do first ( i.e., the concatenation; then the regular expression ). David J. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
