Received: from mx99.openu.ac.il (arbel.openu.ac.il [147.233.32.4]) by hub.org (8.9.3/8.9.3) with ESMTP id IAA42640; Mon, 26 Jul 1999 08:39:01 -0400 (EDT) (envelope-from herouth@oumail.openu.ac.il) Received: from talmon.openu.ac.il (talmon.openu.ac.il [147.233.32.11]) by mx99.openu.ac.il (Postfix) with ESMTP id DDD8F4B586; Mon, 26 Jul 1999 15:38:59 +0300 (IDT) Received: from [147.233.159.109] (mac-herouth.pc.openu.ac.il) by talmon.openu.ac.il (Netscape Messaging Server 3.54) with ESMTP id AAA1344; Mon, 26 Jul 1999 15:42:18 +0300 X-Sender: herouth@shaked.cc.openu.ac.il Message-Id: In-Reply-To: <008801bed726$ffdc2860$ac0bfea9@dwilson> Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Date: Mon, 26 Jul 1999 15:39:36 +0300 To: "Dan Wilson" , "pgsql general" , pgsql-sql@postgreSQL.org From: Herouth Maoz Subject: Re: [GENERAL] escaping wildcard chars At 08:16 +0300 on 26/07/1999, Dan Wilson wrote: > SELECT typname from pg_type WHERE NOT LIKE '_%' > > It gives me an empty set. > > Is there any way to escape the underscore. I tried to use '\_%', but that > didn't help. Any suggestions? Yes. Use '\\_%' instead. You see, a backslash is interpreted immediately as "take the next char literally". This passes the underscore literally to LIKE. Which is the same as '_%'. If you put a double backslash, the first backslash takes the second one literally, thus passing '\_%' to LIKE. And then LIKE knows that it should treat the underscore as non-special. Ugly, ugly. I think we had a discussion either here or in one of the other lists regarding the ESCAPE clause to LIKE. This behavior means that even if we write ESCAPE '\', it won't work (Actually, it should be '\\'. I really hate those backslashes. They are blatantly incompatible with SQL92 and will cause standard SQL to fail on Postgres, (and of course, Postgres code to fail on other databases). There should be a setting, either in the backend or in a SET command, such as "BACKSLASH_BEHAVIOR", which will be either "literal" or "escape". It can default to the current behavior (namely "escape") so that current code won't fail, but will enable people to write sane standard code. Herouth -- Herouth Maoz, Internet developer. Open University of Israel - Telem project http://telem.openu.ac.il/~herutma