Обсуждение: 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