Re: [SQL] How match percent sign in SELECT using LIKE? - Mailing list pgsql-sql

Dan Lauterbach <danla@micromotion.com> 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    <umehlig@zmt.uni-bremen.de>
              Center for Tropical Marine Ecology/ZMT, Bremen, Germany
----------------------------------------------------------------------