Postgres Pro
Downloads
Home   >   mailing lists

Thread: SELECT from a list

SELECT from a list

From
Keith Gallant
Date:
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

Re: SELECT from a list

From
Markus Bertheau
Date:
В Вск, 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>

Re: SELECT from a list

From
Jean-Luc Lachance
Date:

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]';

>

Re: SELECT from a list

From
Markus Bertheau
Date:
В Вск, 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>

Re: SELECT from a list

From
Tom Lane
Date:
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

Re: regex engine

From
Markus Bertheau
Date:
В Вск, 25.07.2004, в 19:34, Tom Lane пишет:
> 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?

I wasn't trying to say that pg's regex engine was particularly slow, but
that regex engines in general are slower than an exact substring search.
If I'm mistaken here, please tell. pg's regex engine does have one
shortcoming though: it doesn't know UTF-8.

--
Markus Bertheau <twanger@bluetwanger.de>

Re: regex engine

From
Tom Lane
Date:
Markus Bertheau <twanger@bluetwanger.de> writes:
> pg's regex engine does have one
> shortcoming though: it doesn't know UTF-8.

Sure it does.  We borrowed it from Tcl, remember?

The "character class" stuff is not locale-aware at the moment,
which is something that ought to get fixed eventually, but claiming
it doesn't handle UTF8 at all is simply wrong.
        regards, tom lane

Re: regex engine

From
Markus Bertheau
Date:
В Вск, 25.07.2004, в 21:41, Tom Lane пишет:
> Markus Bertheau <twanger@bluetwanger.de> writes:
> > pg's regex engine does have one
> > shortcoming though: it doesn't know UTF-8.
>
> Sure it does.  We borrowed it from Tcl, remember?
>
> The "character class" stuff is not locale-aware at the moment,
> which is something that ought to get fixed eventually, but claiming
> it doesn't handle UTF8 at all is simply wrong.

Turns out I tested in a SQL_ASCII database. Sorry :)

--
Markus Bertheau <twanger@bluetwanger.de>