From: | "David Johnston" <polobo(at)yahoo(dot)com> |
---|---|
To: | "'Gauthier, Dave'" <dave(dot)gauthier(at)intel(dot)com>, "'John R Pierce'" <pierce(at)hogranch(dot)com>, "'PostgreSQL'" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: How to use like with a list |
Date: | 2011-11-18 20:58:52 |
Message-ID: | 056501cca634$e15f6f60$a41e4e20$@yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Gauthier, Dave
Sent: Friday, November 18, 2011 3:37 PM
To: John R Pierce; PostgreSQL
Subject: Re: [GENERAL] 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
--
Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
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.
From | Date | Subject | |
---|---|---|---|
Next Message | John R Pierce | 2011-11-18 20:59:32 | Re: operator precedence (was: How to use like with a list) |
Previous Message | John R Pierce | 2011-11-18 20:47:16 | Re: How to use like with a list |