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