It finally dawned on my how to easily implement the LIKE/ESCAPE clause.
Currently, LIKE is transformed to the "~~" operator in the parser. For
LIKE/ESCAPE, we should instead transform it to a three-parameter
function call. The rest of the implementation is likely to be trivial
(as is this parsing solution).

Does anyone see a problem with this solution? Should I also change the
existing "two parameter" implementation to look for a function call
instead of an operator (I think so, but...)?

Someone has been working on an "SQL generator function", which will be
used to generate output (??). The "like()" function should be
transformed back to the SQL9x clause; any hints on where to look (or
volunteers to fix that part)?

                     - Thomas

Sat, 05 Aug 2000 06:23:55 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
LIKE/ESCAPE implementation
At 01:23 AM 8/5/2000, Thomas Lockhart wrote:
>It finally dawned on my how to easily implement the LIKE/ESCAPE clause.
>Currently, LIKE is transformed to the "~~" operator in the parser. For
>LIKE/ESCAPE, we should instead transform it to a three-parameter
>function call. The rest of the implementation is likely to be trivial
>(as is this parsing solution).

While your at it... :)

Would their be anything like an ILIKE for a case insensitive like 
search?   Or maybe insensitive over text/char/varchar datatypes?

Just a thought...

Thomas

-
- Thomas Swan
- Graduate Student  - Computer Science
- The University of Mississippi
-
- "People can be categorized into two fundamental
- groups, those that divide people into two groups
- and those that don't."

At 01:23 AM 8/5/2000, Thomas Lockhart wrote:
It finally dawned on my how to easily
implement the LIKE/ESCAPE clause.
Currently, LIKE is transformed to the "~~" operator in the
parser. For
LIKE/ESCAPE, we should instead transform it to a three-parameter
function call. The rest of the implementation is likely to be
trivial
(as is this parsing solution).

While your at it... :)   

Would their be anything like an ILIKE for a case insensitive like
search?   Or maybe insensitive over text/char/varchar
datatypes?

Just a thought...

Thomas


- 
- Thomas Swan
                                  
- Graduate Student  - Computer Science
- The University of Mississippi
- 
- "People can be categorized into two fundamental 
- groups, those that divide people into two groups 
- and those that don't."
Sat, 05 Aug 2000 01:26:00 -0500
Thomas Swan <tswan@olemiss.edu>
Re: LIKE/ESCAPE implementation
> Would their be anything like an ILIKE for a case insensitive like
> search?   Or maybe insensitive over text/char/varchar datatypes?

What is ILIKE? afaik it is not in SQL9x, so is there any reason to have
that rather than the full regular expression case-insensitive operator
("~*") we already have?

                    - Thomas

Sat, 05 Aug 2000 06:59:42 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: LIKE/ESCAPE implementation
> > Would their be anything like an ILIKE for a case insensitive like
> > search?   Or maybe insensitive over text/char/varchar datatypes?
> 
> What is ILIKE?

As far as I remember it was introduced in Oracle. (I may be mistaken)

> afaik it is not in SQL9x, so is there any reason to have
> that rather than the full regular expression case-insensitive operator
> ("~*") we already have?

Yes. They are. If you use RE you should escape lots of symbols. If you do not need
power of RE ILIKE is really the best choice.

-- 
Sincerely Yours,
Denis Perchine

----------------------------------
E-Mail: dyp@perchine.com
HomePage: http://www.perchine.com/dyp/
FidoNet: 2:5000/120.5
----------------------------------

Sat, 5 Aug 2000 14:08:58 +0700
Denis Perchine <dyp@perchine.com>
Re: LIKE/ESCAPE implementation
Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
> What is ILIKE? afaik it is not in SQL9x, so is there any reason to have
> that rather than the full regular expression case-insensitive operator
> ("~*") we already have?

Just that a lot of people have asked for it, over and over again ...
see the archives ...

			regards, tom lane

Sat, 05 Aug 2000 22:37:10 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: LIKE/ESCAPE implementation 
> > What is ILIKE? afaik it is not in SQL9x, so is there any reason to have
> > that rather than the full regular expression case-insensitive operator
> > ("~*") we already have?
> Just that a lot of people have asked for it, over and over again ...
> see the archives ...

I had thought it would be trivial to do ILIKE, but now I'm not sure how
to handle the multi-byte case. It isn't sufficient to wrap the
single-byte comparison arguments with tolower() is it??

btw, do the archives have a full discussion of the correct syntax for
this? I recall people asking for it, but since it is a non-standard
feature what implementation example should I follow? What alternatives
are there? Is "check the archives" sufficient to produce a complete
design discussion? What thread??

                      - Thomas

Sun, 06 Aug 2000 03:45:57 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: LIKE/ESCAPE implementation
Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
> I had thought it would be trivial to do ILIKE, but now I'm not sure how
> to handle the multi-byte case. It isn't sufficient to wrap the
> single-byte comparison arguments with tolower() is it??

I'd be inclined to force both strings to lower case as a whole and
then apply normal LIKE.  Comments anyone?

> I recall people asking for it, but since it is a non-standard
> feature what implementation example should I follow? What alternatives
> are there? Is "check the archives" sufficient to produce a complete
> design discussion?

I do not recall seeing a complete proposal, but wasn't someone just
opining that Oracle has such a feature?  If so, borrowing their spec
seems the thing to do.

			regards, tom lane

Sun, 06 Aug 2000 00:10:10 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: LIKE/ESCAPE implementation 
At 10:45 PM 8/5/2000, Thomas Lockhart wrote:
> > > What is ILIKE? afaik it is not in SQL9x, so is there any reason to have
> > > that rather than the full regular expression case-insensitive operator
> > > ("~*") we already have?
> > Just that a lot of people have asked for it, over and over again ...
> > see the archives ...
>
>I had thought it would be trivial to do ILIKE, but now I'm not sure how
>to handle the multi-byte case. It isn't sufficient to wrap the
>single-byte comparison arguments with tolower() is it??
>
>btw, do the archives have a full discussion of the correct syntax for
>this? I recall people asking for it, but since it is a non-standard
>feature what implementation example should I follow? What alternatives
>are there? Is "check the archives" sufficient to produce a complete
>design discussion? What thread??

I don't know... As far as syntax would go, I would follow the existing LIKE 
operator, doing a case insensitive operation.


-
- Thomas Swan
- Graduate Student  - Computer Science
- The University of Mississippi
-
- "People can be categorized into two fundamental
- groups, those that divide people into two groups
- and those that don't."

At 10:45 PM 8/5/2000, Thomas Lockhart wrote:
> > What is ILIKE? afaik it is not in
SQL9x, so is there any reason to have
> > that rather than the full regular expression case-insensitive
operator
> > ("~*") we already have?
> Just that a lot of people have asked for it, over and over again
...
> see the archives ...

I had thought it would be trivial to do ILIKE, but now I'm not sure
how
to handle the multi-byte case. It isn't sufficient to wrap the
single-byte comparison arguments with tolower() is it??

btw, do the archives have a full discussion of the correct syntax
for
this? I recall people asking for it, but since it is a non-standard
feature what implementation example should I follow? What
alternatives
are there? Is "check the archives" sufficient to produce a
complete
design discussion? What thread??

I don't know... As far as syntax would go, I would follow the existing
LIKE operator, doing a case insensitive operation.



- 
- Thomas Swan
                                  
- Graduate Student  - Computer Science
- The University of Mississippi
- 
- "People can be categorized into two fundamental 
- groups, those that divide people into two groups 
- and those that don't."
Sat, 05 Aug 2000 23:14:52 -0500
Thomas Swan <tswan@olemiss.edu>
Re: LIKE/ESCAPE implementation
> > I had thought it would be trivial to do ILIKE, but now I'm not sure how
> > to handle the multi-byte case. It isn't sufficient to wrap the
> > single-byte comparison arguments with tolower() is it??
> I'd be inclined to force both strings to lower case as a whole and
> then apply normal LIKE.  Comments anyone?

OK. "Both strings to lower case as a whole" doesn't seem to be something
which is multibyte-enabled in our code. Am I just missing seeing some
features? istm that lots of our code falls over on MB strings...

> I do not recall seeing a complete proposal, but wasn't someone just
> opining that Oracle has such a feature?  If so, borrowing their spec
> seems the thing to do.

Anyone have suggestions for a reference? Altavista on "+ilike +oracle"
doesn't seem to do it.

                   - Thomas

Sun, 06 Aug 2000 04:51:05 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: LIKE/ESCAPE implementation
Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
>> I'd be inclined to force both strings to lower case as a whole and
>> then apply normal LIKE.  Comments anyone?

> OK. "Both strings to lower case as a whole" doesn't seem to be something
> which is multibyte-enabled in our code. Am I just missing seeing some
> features?

Not sure that it matters for multibyte, but for sure LOCALE ought to
make a difference.  Consider German esstet (sp?) --- that beta-looking
symbol that lowercases to "ss".  Do we do this correctly?

			regards, tom lane

Sun, 06 Aug 2000 01:03:09 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: LIKE/ESCAPE implementation 
> Not sure that it matters for multibyte, but for sure LOCALE ought to
> make a difference.  Consider German esstet (sp?) --- that beta-looking
> symbol that lowercases to "ss".  Do we do this correctly?

afaict we do none of this. Using tolower() on a char* variable can not
possibly do the right thing for multiple-byte character sets. Your
example (single byte to two bytes) can't work either.

Tatsuo and others: what is the state of MB for these cases? Should I
just code the single-byte LOCALE solution for now, or do we have some
other code I should be referring to?

                   - Thomas

Sun, 06 Aug 2000 05:38:06 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: LIKE/ESCAPE implementation