Received: from candle.pha.pa.us (maillist@s5-03.ppp.op.net [209.152.195.67]) by hub.org (8.9.2/8.9.1) with ESMTP id PAA60434 for ; Tue, 16 Mar 1999 15:31:17 -0500 (EST) (envelope-from maillist@candle.pha.pa.us) Received: (from maillist@localhost) by candle.pha.pa.us (8.9.0/8.9.0) id PAA18319; Tue, 16 Mar 1999 15:24:50 -0500 (EST) From: Bruce Momjian Message-Id: <199903162024.PAA18319@candle.pha.pa.us> Subject: Re: [SQL] How match percent sign in SELECT using LIKE? In-Reply-To: from Herouth Maoz at "Mar 16, 1999 1:52:52 pm" To: herouth@oumail.openu.ac.il (Herouth Maoz) Date: Tue, 16 Mar 1999 15:24:50 -0500 (EST) Cc: pgsql-sql@postgreSQL.org X-Mailer: ELM [version 2.4ME+ PL47 (25)] MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit Yes, you are correct. However, since '\' is an escape in all our strings, it makes sense to use it as the default escape in LIKE. I agree we should allow ESC, but that would make LIKE a trinary operation, rather than a binary. If you want really confusing, the code for LIKE really does: | a_expr LIKE a_expr { $$ = makeIndexable("~~", $1, $3); } so it maps LIKE to a binary operator "~~". How do we map that into a trinary operator, which we don't support? Doesn't really seem worth it. I can add an item to the TODO list if you wish? > At 17:45 +0200 on 15/3/99, Bruce Momjian wrote: > > > > I have overhauled the LIKE code. %% is not a literal %, but is the same > > as wildcard %. Literal % is \%. > > This is not SQL 92 compliant, is it? The standard states that if you don't > have an ESCAPE part to the like (i.e. abc LIKE 'bla\%%' ESCAPE '\'), then > there is no escape. I think the correct thing to do is to support the > ESCAPE clause: > > - - - begin quotation - - - > > Format > > ::= > [ NOT ] LIKE > [ ESCAPE ] > > ::= > > ::= > > ::= > > [snip] > > a) If an is specified, then: > > i) If the length in characters of E is not equal to 1, then > an exception condition is raised: data exception-invalid > escape character. > > ii) If there is not a partitioning of the string P into sub- > strings such that each substring has length 1 or 2, no > substring of length 1 is the escape character E, and each > substring of length 2 is the escape character E followed by > either the escape character E, an character, > or the character, then an exception condition is > raised: data exception-invalid escape sequence. > > If there is such a partitioning of P, then in that parti- > tioning, each substring with length 2 represents a single > occurrence of the second character of that substring. Each > substring with length 1 that is the character > represents an arbitrary character specifier. Each substring > with length 1 that is the character represents > an arbitrary string specifier. Each substring with length > 1 that is neither the character nor the cent> character represents the character that it contains. > > b) If an is not specified, then each score> character in P represents an arbitrary character spec- > ifier, each character in P represents an arbitrary > string specifier, and each character in P that is neither the > character nor the character represents > itself. > > - - - end quotation - - - > > Herouth > > -- > Herouth Maoz, Internet developer. > Open University of Israel - Telem project > http://telem.openu.ac.il/~herutma > > > -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026