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 KAA52762 for ; Mon, 15 Mar 1999 10:46:16 -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 KAA14287; Mon, 15 Mar 1999 10:45:28 -0500 (EST) From: Bruce Momjian Message-Id: <199903151545.KAA14287@candle.pha.pa.us> Subject: Re: [SQL] How match percent sign in SELECT using LIKE? In-Reply-To: <199903110845.JAA02036@pandora3.uni-bremen.de> from Ulf Mehlig at "Mar 11, 1999 9:45:45 am" To: umehlig@uni-bremen.de (Ulf Mehlig) Date: Mon, 15 Mar 1999 10:45:28 -0500 (EST) Cc: danla@micromotion.com, pgsql-sql@postgreSQL.org X-Mailer: ELM [version 2.4ME+ PL47 (25)] MIME-Version: 1.0 Content-Type: text/plain; charset=UNKNOWN-8BIT Content-Transfer-Encoding: 8bit I have overhauled the LIKE code. %% is not a literal %, but is the same as wildcard %. Literal % is \%. > Dan Lauterbach wrote: > > > How do I match '%' in a SELECT query using LIKE predicate? For > > example, to query for DocNo's containing string 'EW%': > > > > SELECT * FROM XXXX WHERE DocNo LIKE '%EW%%'; > > > > PostgreSQL wants to treat the '%' in 'EW%' as a wildcard. I've tried > > escaping the '%' using '\%', > > '%%'. The SQL-92 standard provides for this using the ESCAPE keyword: > > > > SELECT * FROM XXXX WHERE DocNo LIKE '%EW#%%' ESCAPE '#'; > > You apparently *can* use the '%' itself to mask the '%'. I read that > somewhere, but I don't find it in PostgreSQL's documentation now. > > db=> create table xxx (x text); > db=> insert into xxx (x) values ('aaabbbccc'); > db=> insert into xxx (x) values ('aaabbb%ccc'); > db=> insert into xxx (x) values ('aaabbb%%ccc'); > > - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - > Normal use of % as wildcard: > > db=> select * from xxx where x like 'aaa%' order by 1; > x > ----------- > aaabbb%%ccc > aaabbb%ccc > aaabbbccc > (3 rows) > - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - > Escaping ... > > db=> select * from xxx where x like 'aaabbb%%ccc' order by 1; > x > ---------- > aaabbb%ccc > (1 row) > > db=> select * from xxx where x like 'aaabbb%%%%ccc' order by 1; > x > ----------- > aaabbb%%ccc > (1 row) > > db=> select * from xxx where x like 'aaabbb%%%%%%ccc' order by 1; > x > - > (0 rows) > - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - > This here I consider strange (shouldn't it only escape and not > 'wildcard' additionally?!) > > db=> select * from xxx where x like 'aaabbb%%' order by 1; > x > ----------- > aaabbb%%ccc > aaabbb%ccc > (2 rows) > > db=> select * from xxx where x like 'aaabbb%%c' order by 1; > x > ---------- > aaabbb%ccc > (1 row) > > db=> select * from xxx where x like 'aaabbb%%cc' order by 1; > x > ---------- > aaabbb%ccc > (1 row) > > db=> select * from xxx where x like 'aaabbb%%ccc' order by 1; > x > ---------- > aaabbb%ccc > (1 row) > > db=> select * from xxx where x like 'aaabbb%%cccc' order by 1; > x > - > (0 rows) > - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - > > Anyway, you can use in PostgreSQL regular expressions as well: > > => select * from xxx where x ~ 'aa*b{3}%c+' order by 1; > x > ---------- > aaabbb%ccc > (1 row) > > Much better, if you know regexps. But if I remember correctly, only > 'LIKE ...'- and regular expressions which begin with a constant, > not-wildcard-part can be used for indexed search (other people > certainly know that much better than me ;-) > > Tschüß, Ulf > > -- > ====================================================================== > Ulf Mehlig > Center for Tropical Marine Ecology/ZMT, Bremen, Germany > ---------------------------------------------------------------------- > > -- 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