Received: from alf.zfn.uni-bremen.de (alf.zfn.uni-bremen.de [134.102.20.22]) by hub.org (8.9.2/8.9.1) with ESMTP id DAA35067 for ; Thu, 11 Mar 1999 03:44:06 -0500 (EST) (envelope-from umehlig@uni-bremen.de) Received: from pandora3.uni-bremen.de (umehlig@moritz25.zfn.uni-bremen.de [134.102.6.65]) by alf.zfn.uni-bremen.de (8.9.1a/ZfNServer) with ESMTP id JAA86608; Thu, 11 Mar 1999 09:40:22 +0100 Received: (from umehlig@localhost) by pandora3.uni-bremen.de (8.9.1/8.9.1) id JAA02036; Thu, 11 Mar 1999 09:45:45 +0100 Date: Thu, 11 Mar 1999 09:45:45 +0100 From: Ulf Mehlig Message-Id: <199903110845.JAA02036@pandora3.uni-bremen.de> X-Authentication-Warning: pandora3.uni-bremen.de: umehlig set sender to umehlig@pandora3.uni-bremen.de using -f To: danla@micromotion.com CC: pgsql-sql@postgreSQL.org In-reply-to: <36E6EAE8.4F163DA7@micromotion.com> (danla@micromotion.com) Subject: Re: [SQL] How match percent sign in SELECT using LIKE? References: <36E6EAE8.4F163DA7@micromotion.com> 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 ----------------------------------------------------------------------