Обсуждение: How to use like with a list

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