Received: from netcom15.netcom.com (zalman@netcom15.netcom.com [192.100.81.128]) by hub.org (8.9.2/8.9.1) with ESMTP id PAA62918 for ; Tue, 16 Mar 1999 15:54:30 -0500 (EST) (envelope-from zalman@netcom.com) Received: (from zalman@localhost) by netcom15.netcom.com (8.8.5-r-beta/8.8.5/(NETCOM v1.02)) id MAA01651; Tue, 16 Mar 1999 12:51:42 -0800 (PST) From: Zalman Stern Message-Id: <199903162051.MAA01651@netcom15.netcom.com> Subject: Re: [SQL] How match percent sign in SELECT using LIKE? To: maillist@candle.pha.pa.us (Bruce Momjian) Date: Tue, 16 Mar 1999 12:51:41 -0800 (PST) Cc: herouth@oumail.openu.ac.il, pgsql-sql@postgreSQL.org In-Reply-To: <199903162024.PAA18319@candle.pha.pa.us> from "Bruce Momjian" at Mar 16, 99 03:24:50 pm X-Mailer: ELM [version 2.4 PL25] MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit Bruce Momjian wrote: > 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? One useful and probably not too hard thing to do is to allow ESCAPE '\' on the end of the LIKE clause. Any character other than '\' will be an error. This allows Postgres users to write compliant SQL code that can be used with other databases. Another approach is to "rewrite" the match string at parse time. If it is a known constant, you can do the whole job there. Otherwise, you'd insert an extra node in the parse tree which does the rewrite just before calling hte "~~" operator. (I am assuming the match string can be a general expression and that you can add a function of two arguments which rewrites the first argument using the second argument as the escape character. This is of course not the utmost of micro efficiency, but I doubt it would matter much.) But I don't have in depth knowledge of the Postgres SQL parser and evaluator so I may be way off base. -Z-