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(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of John R Pierce
Sent: Friday, November 18, 2011 3:23 PM
To: PostgreSQL
Subject: Re: [GENERAL] 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(at)hogranch(dot)com> 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