Обсуждение: LIKE/ESCAPE implementation

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

At 01:23 AM 8/5/2000, Thomas Lockhart wrote:<br /><blockquote cite="cite" type="cite">It finally dawned on my how to
easilyimplement the LIKE/ESCAPE clause.<br /> Currently, LIKE is transformed to the "~~" operator in the parser. For<br
/>LIKE/ESCAPE, we should instead transform it to a three-parameter<br /> function call. The rest of the implementation
islikely to be trivial<br /> (as is this parsing solution).<br /></blockquote><br /> While your at it... :)   <br /><br
/>Would their be anything like an ILIKE for a case insensitive like search?   Or maybe insensitive over
text/char/varchardatatypes?<br /><br /> Just a thought...<br /><br /> Thomas<br /><br /> - <br /> - <b><u>Thomas
Swan</u></b>                                  <br /> - Graduate Student  - Computer Science<br /> - The University of
Mississippi<br/> - <br /> - "People can be categorized into two fundamental <br /> - groups, those that divide people
intotwo groups <br /> - and those that don't." 

> 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

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

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

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

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

At 10:45 PM 8/5/2000, Thomas Lockhart wrote:<br /><blockquote cite="cite" type="cite">> > What is ILIKE? afaik
itis not in SQL9x, so is there any reason to have<br /> > > that rather than the full regular expression
case-insensitiveoperator<br /> > > ("~*") we already have?<br /> > Just that a lot of people have asked for
it,over and over again ...<br /> > see the archives ...<br /><br /> I had thought it would be trivial to do ILIKE,
butnow I'm not sure how<br /> to handle the multi-byte case. It isn't sufficient to wrap the<br /> single-byte
comparisonarguments with tolower() is it??<br /><br /> btw, do the archives have a full discussion of the correct
syntaxfor<br /> this? I recall people asking for it, but since it is a non-standard<br /> feature what implementation
exampleshould I follow? What alternatives<br /> are there? Is "check the archives" sufficient to produce a complete<br
/>design discussion? What thread??<br /></blockquote><br /> I don't know... As far as syntax would go, I would follow
theexisting LIKE operator, doing a case insensitive operation.<br /><br /><br /> - <br /> - <b><u>Thomas Swan</u></b>
                                  <br/> - Graduate Student  - Computer Science<br /> - The University of Mississippi<br
/>- <br /> - "People can be categorized into two fundamental <br /> - groups, those that divide people into two groups
<br/> - and those that don't." 

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

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

> 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