pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Bruce Momjian <maillist@candle.pha.pa.us>
To: umehlig@uni-bremen.de
Cc: danla@micromotion.com
Cc: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] How match percent sign in SELECT using LIKE?
Date: Mon, 15 Mar 1999 10:45:28 -0500 (EST)
Message-ID: <199903151545.KAA14287@candle.pha.pa.us> (raw)
In-Reply-To: <199903110845.JAA02036@pandora3.uni-bremen.de>


I have overhauled the LIKE code.  %% is not a literal %, but is the same
as wildcard %.  Literal % is \%.


> 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
> ----------------------------------------------------------------------
> 
> 


-- 
  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



view thread (15+ messages)  latest in thread

Message-ID: <199903151545.KAA14287@candle.pha.pa.us>
Permalink:  ../199903151545.KAA14287@candle.pha.pa.us/
Also on:    postgresql.org/message-id/199903151545.KAA14287@candle.pha.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: maillist@candle.pha.pa.us, umehlig@uni-bremen.de, danla@micromotion.com
  Subject: Re: [SQL] How match percent sign in SELECT using LIKE?
  In-Reply-To: <199903151545.KAA14287@candle.pha.pa.us>
  References: <199903110845.JAA02036@pandora3.uni-bremen.de> <199903151545.KAA14287@candle.pha.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox