From: Jasen Betts <jasen@xnet.co.nz>
To: pgsql-sql@postgresql.org
Subject: Re: where col1 not ilike ('str1%', 'str2%'... 'strN%') support?
Date: 31 Aug 2011 10:17:50 GMT
Message-ID: <j3l1oe$r4i$1@reversiblemaps.ath.cx> (raw)
References: <4E5CF8F8.7030404@encs.concordia.ca>
<16967.1314717876@sss.pgh.pa.us>
<4E5D0656.8090709@encs.concordia.ca>
<17497.1314719596@sss.pgh.pa.us>
<4E5D0856.105@encs.concordia.ca>
On 2011-08-30, Emi Lu <emilu@encs.concordia.ca> wrote:
> Hi Tom,
>
>>>> select * from tablename
>>>> where not (col1 ~~* any(array['str1%', 'str2%'... 'strN%']));
>>
>>> If next version could have "not ilike ('', '')" added into window
>>> functions, that's will be great!
>>
>> Why? And what's this got to do with window functions?
>
> First, where not (col1 ~~* any(array['str1%', 'str2%'... 'strN%'])) will
> work for me.
>
> But I feel " ilike ('str1', ... 'strN')" is more intuitive, isn't it?
no, "('str1', ... 'strN')" looks a lot like a tuple.
if you know these strings beforehand use a single regular expression
instead because ilike is just regex in drag (postgres uses regex to do
ilike), and while ilike can only check one pattern at a time regex can
check several simultaneously.
foo ~* '^(str1|str2|str3...|strN)$'
--
⚂⚃ 100% natural
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jasen@xnet.co.nz
Subject: Re: where col1 not ilike ('str1%', 'str2%'... 'strN%') support?
In-Reply-To: <j3l1oe$r4i$1@reversiblemaps.ath.cx>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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