Обсуждение: 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] 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 -- 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