SELECT from a list

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



---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Re: SELECT from a list

В Вск, 25.07.2004, в 15:18, Keith Gallant п=
ишет:
> Hello
>=20
> I am wondering if it is possible to use a SINGLE LIKE statement for a
> selection from a list.
>=20
> 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:
>=20
> SELECT * FROM table WHERE phrase LIKE
> {'0%','1%','2%','3%','4%','5%','6%','7%','8%','9%'};
>=20
> If not is there an easier way than having to call this:
>=20
> 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', ....)

--=20
Markus Bertheau


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: SELECT from a list

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

>


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Re: SELECT from a list

В Вск, 25.07.2004, в 16:40, Jean-Luc Lachance =D0=
¿Ð¸ÑˆÐµÑ=82:

> Better yet:
>=20
> SELECT * FROM table WHERE phrase ~ '^[0-9]';

Not so sure if that's better - the regex engines aren't the fastest.

--=20
Markus Bertheau


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: SELECT from a list

Markus Bertheau 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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Re: regex engine

В Вск, 25.07.2004, в 19:34, Tom Lane пи=
шет:
> Markus Bertheau writes:
> > В Вск, 25.07.2004, в 16:40, Jean-Luc Lachance =
пишет:
> >> Better yet:
> >>=20
> >> SELECT * FROM table WHERE phrase ~ '^[0-9]';
>=20
> > Not so sure if that's better - the regex engines aren't the fastest.
>=20
> [ 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.

--=20
Markus Bertheau


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Re: regex engine

Markus Bertheau 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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Re: regex engine

В Вск, 25.07.2004, в 21:41, Tom Lane пи=
шет:
> Markus Bertheau writes:
> > pg's regex engine does have one
> > shortcoming though: it doesn't know UTF-8.
>=20
> Sure it does. We borrowed it from Tcl, remember?
>=20
> 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 :)

--=20
Markus Bertheau


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend