pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSELECT from a list
5+ messages / 4 participants
[nested] [flat]
* SELECT from a list
@ 2004-07-25 13:18 Keith Gallant <keith@justhosting.ca>
0 siblings, 1 reply; 5+ messages in thread
From: Keith Gallant @ 2004-07-25 13:18 UTC (permalink / raw)
To: pgsql-sql
Hello
I am wondering if it is possible to use a SINGLE LIKE statement for a
selection from a list.
For example: If I want to return all results that a phrase starts with a
number, can I make a call similar to the following:
SELECT * FROM table WHERE phrase LIKE
{'0%','1%','2%','3%','4%','5%','6%','7%','8%','9%'};
If not is there an easier way than having to call this:
SELECT * FROM table WHERE phrase LIKE '0%' OR phrase LIKE '1%' OR phrase
LIKE '2%' OR phrase LIKE '3%' OR phrase LIKE '4%' OR phrase LIKE '5%' OR
phrase LIKE '6%' OR phrase LIKE '7%' OR phrase LIKE '8%' OR phrase LIKE
'9%';
Thank you.
Keith
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: SELECT from a list
@ 2004-07-25 13:56 Markus Bertheau <twanger@bluetwanger.de>
parent: Keith Gallant <keith@justhosting.ca>
0 siblings, 1 reply; 5+ messages in thread
From: Markus Bertheau @ 2004-07-25 13:56 UTC (permalink / raw)
To: Keith Gallant <keith@justhosting.ca>; +Cc: pgsql-sql
В Вск, 25.07.2004, в 15:18, Keith Gallant пишет:
> Hello
>
> I am wondering if it is possible to use a SINGLE LIKE statement for a
> selection from a list.
>
> For example: If I want to return all results that a phrase starts with a
> number, can I make a call similar to the following:
>
> SELECT * FROM table WHERE phrase LIKE
> {'0%','1%','2%','3%','4%','5%','6%','7%','8%','9%'};
>
> If not is there an easier way than having to call this:
>
> SELECT * FROM table WHERE phrase LIKE '0%' OR phrase LIKE '1%' OR phrase
> LIKE '2%' OR phrase LIKE '3%' OR phrase LIKE '4%' OR phrase LIKE '5%' OR
> phrase LIKE '6%' OR phrase LIKE '7%' OR phrase LIKE '8%' OR phrase LIKE
> '9%';
WHERE SUBSTRING(phrase FROM 1 FOR 1) IN ('0', '1', ....)
--
Markus Bertheau <twanger@bluetwanger.de>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: SELECT from a list
@ 2004-07-25 14:40 Jean-Luc Lachance <jllachan@sympatico.ca>
parent: Markus Bertheau <twanger@bluetwanger.de>
0 siblings, 1 reply; 5+ messages in thread
From: Jean-Luc Lachance @ 2004-07-25 14:40 UTC (permalink / raw)
To: Markus Bertheau <twanger@bluetwanger.de>; +Cc: Keith Gallant <keith@justhosting.ca>; pgsql-sql
Markus Bertheau wrote:
> В Вск, 25.07.2004, в 15:18, Keith Gallant пишет:
>
>>Hello
>>
>>I am wondering if it is possible to use a SINGLE LIKE statement for a
>>selection from a list.
>>
>>For example: If I want to return all results that a phrase starts with a
>>number, can I make a call similar to the following:
>>
>>SELECT * FROM table WHERE phrase LIKE
>>{'0%','1%','2%','3%','4%','5%','6%','7%','8%','9%'};
>>
>>If not is there an easier way than having to call this:
>>
>>SELECT * FROM table WHERE phrase LIKE '0%' OR phrase LIKE '1%' OR phrase
>>LIKE '2%' OR phrase LIKE '3%' OR phrase LIKE '4%' OR phrase LIKE '5%' OR
>>phrase LIKE '6%' OR phrase LIKE '7%' OR phrase LIKE '8%' OR phrase LIKE
>>'9%';
>
>
> WHERE SUBSTRING(phrase FROM 1 FOR 1) IN ('0', '1', ....)
Better yet:
SELECT * FROM table WHERE phrase ~ '^[0-9]';
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: SELECT from a list
@ 2004-07-25 16:21 Markus Bertheau <twanger@bluetwanger.de>
parent: Jean-Luc Lachance <jllachan@sympatico.ca>
0 siblings, 1 reply; 5+ messages in thread
From: Markus Bertheau @ 2004-07-25 16:21 UTC (permalink / raw)
To: Jean-Luc Lachance <jllachan@sympatico.ca>; +Cc: Keith Gallant <keith@justhosting.ca>; pgsql-sql
В Вск, 25.07.2004, в 16:40, Jean-Luc Lachance пишет:
> Better yet:
>
> SELECT * FROM table WHERE phrase ~ '^[0-9]';
Not so sure if that's better - the regex engines aren't the fastest.
--
Markus Bertheau <twanger@bluetwanger.de>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: SELECT from a list
@ 2004-07-25 17:34 Tom Lane <tgl@sss.pgh.pa.us>
parent: Markus Bertheau <twanger@bluetwanger.de>
0 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2004-07-25 17:34 UTC (permalink / raw)
To: Markus Bertheau <twanger@bluetwanger.de>; +Cc: Jean-Luc Lachance <jllachan@sympatico.ca>; Keith Gallant <keith@justhosting.ca>; pgsql-sql
Markus Bertheau <twanger@bluetwanger.de> writes:
> В Вск, 25.07.2004, в 16:40, Jean-Luc Lachance пишет:
>> Better yet:
>>
>> SELECT * FROM table WHERE phrase ~ '^[0-9]';
> Not so sure if that's better - the regex engines aren't the fastest.
[ raised eyebrow... ] I was under the impression that we had a pretty
good one as of PG 7.4. Have you tested it lately?
It is true that the above won't be indexable whereas the more tedious OR
form potentially could use an index. This is not the fault of the regex
engine however, but of limited understanding of regexes in the planner.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2004-07-25 17:34 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-07-25 13:18 SELECT from a list Keith Gallant <keith@justhosting.ca>
2004-07-25 13:56 ` Markus Bertheau <twanger@bluetwanger.de>
2004-07-25 14:40 ` Jean-Luc Lachance <jllachan@sympatico.ca>
2004-07-25 16:21 ` Markus Bertheau <twanger@bluetwanger.de>
2004-07-25 17:34 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox