Re: [SQL] How match percent sign in SELECT using LIKE?

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

> Dan Lauterbach <danla(at)micromotion(dot)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(at)zmt(dot)uni-bremen(dot)de>
> Center for Tropical Marine Ecology/ZMT, Bremen, Germany
> ----------------------------------------------------------------------
>
>

--
Bruce Momjian | http://www.op.net/~candle
maillist(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026