Horton

Thread

  1. LIKE/ESCAPE implementation

    Thomas Lockhart <lockhart@alumni.caltech.edu> — 2000-08-05T06:23:55Z

    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

  2. Re: LIKE/ESCAPE implementation

    Thomas Swan <tswan@olemiss.edu> — 2000-08-05T06:26:00Z

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

  3. Re: LIKE/ESCAPE implementation

    Thomas Lockhart <lockhart@alumni.caltech.edu> — 2000-08-05T06:59:42Z

    > 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

  4. Re: LIKE/ESCAPE implementation

    Denis Perchine <dyp@perchine.com> — 2000-08-05T07:08:58Z

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

  5. Re: LIKE/ESCAPE implementation

    Tom Lane <tgl@sss.pgh.pa.us> — 2000-08-06T02:37:10Z

    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

  6. Re: LIKE/ESCAPE implementation

    Thomas Lockhart <lockhart@alumni.caltech.edu> — 2000-08-06T03:45:57Z

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

  7. Re: LIKE/ESCAPE implementation

    Tom Lane <tgl@sss.pgh.pa.us> — 2000-08-06T04:10:10Z

    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

  8. Re: LIKE/ESCAPE implementation

    Thomas Swan <tswan@olemiss.edu> — 2000-08-06T04:14:52Z

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

  9. Re: LIKE/ESCAPE implementation

    Thomas Lockhart <lockhart@alumni.caltech.edu> — 2000-08-06T04:51:05Z

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

  10. Re: LIKE/ESCAPE implementation

    Tom Lane <tgl@sss.pgh.pa.us> — 2000-08-06T05:03:09Z

    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

  11. Re: LIKE/ESCAPE implementation

    Thomas Lockhart <lockhart@alumni.caltech.edu> — 2000-08-06T05:38:06Z

    > 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