How to use like with a list
Hi:
How can I search on a csv list of values using "like" where each value is to be appended with a wildcarded string?
Example:
list = 'jo,mo,do,fo'
I want to pull all names from a table with name like.. 'jol%' or 'mol%' or'dol%' or 'sol%'
would match "jolly, molly, moleman,dollface, solarboy"
notice they all have the same "l%' wildcarded suffix.
I'm really not that lazy, just trying to keep this in a simgle query in order to minimize network hits which will reduce overall wallclock performance. This thing wil be running in a programming loop.
Thanks in Advance !
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Gauthier, Dave
Sent: Friday, November 18, 2011 2:56 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] How to use like with a list
Hi:
How can I search on a csv list of values using "like" where each value is to
be appended with a wildcarded string?
Example:
list = 'jo,mo,do,fo'
I want to pull all names from a table with name like.. 'jol%' or 'mol%'
or'dol%' or 'sol%'
would match "jolly, molly, moleman,dollface, solarboy"
notice they all have the same "l%' wildcarded suffix.
I'm really not that lazy, just trying to keep this in a simgle query in
order to minimize network hits which will reduce overall wallclock
performance. This thing wil be running in a programming loop.
Thanks in Advance !
Do you need a solution for your specific example or something more
generalized?
You can make use of "substing" (on the input) and "split_to_array" (on the
csv list) to efficiently solve your stated problem (possibly with indexes).
Something more general would be to convert:
'jo,mo,do,fo' INTO '^(jo|mo|do|fo)L.*' (upper case "L" for clarity) and then
use "regexp_matches"
David J.
On 11/18/11 11:55 AM, Gauthier, Dave wrote:
Hi:
How can I search on a csv list of values using "like" where each value
is to be appended with a wildcarded string?Example:
list = 'jo,mo,do,fo'
I want to pull all names from a table with name like.. 'jo*l%*' or
'mo*l%*' or'do*l%*' or 'so*l%*'would match "jolly, molly, moleman,dollface, solarboy"
notice they all have the same "l%' wildcarded suffix.
I'm really not that lazy, just trying to keep this in a simgle query
in order to minimize network hits which will reduce overall wallclock
performance. This thing wil be running in a programming loop.
where field ~ '^(jo|mo|do|fo)'
--
john r pierce N 37, W 122
santa cruz ca mid-left coast
On Fri, Nov 18, 2011 at 12:13 PM, John R Pierce <pierce@hogranch.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'
--
Regards,
Richard Broersma Jr.
On 11/18/11 12:18 PM, Richard Broersma wrote:
On Fri, Nov 18, 2011 at 12:13 PM, John R Pierce<pierce@hogranch.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
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@postgresql.org [mailto:pgsql-general-owner@postgresql.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@hogranch.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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 11/18/11 12:37 PM, Gauthier, Dave wrote:
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
ah, needs () around the right side of the ~ expression, not sure why.
does ~ have higher expression priority than || or something?
select name from test where name ~ ('^(' || replace(?, ',', '|') || ')l');
worked for me.
--
john r pierce N 37, W 122
santa cruz ca mid-left coast
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.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@postgresql.org
[mailto:pgsql-general-owner@postgresql.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@hogranch.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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.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.
On 11/18/11 12:47 PM, John R Pierce wrote:
does ~ have higher expression priority than || or something?
speaking of...
the precedence table [1]http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-PRECEDENCE seems somewhat short of operators... the regex
operators like ~ ~* etc aren't on there, nor is string concatenation ||
... I'd expect the regex pattern ops like ~ should be with the LIKE
etc pattern matching, but apparently they are lumped in with 'everything
else' resulting in the anomalous behavior noted in the referenced thread...
[1]: http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-PRECEDENCE
http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-PRECEDENCE
--
john r pierce N 37, W 122
santa cruz ca mid-left coast
BINGO !
Thanks :-)
-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of John R Pierce
Sent: Friday, November 18, 2011 3:47 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] How to use like with a list
On 11/18/11 12:37 PM, Gauthier, Dave wrote:
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
ah, needs () around the right side of the ~ expression, not sure why.
does ~ have higher expression priority than || or something?
select name from test where name ~ ('^(' || replace(?, ',', '|') || ')l');
worked for me.
--
john r pierce N 37, W 122
santa cruz ca mid-left coast
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general