From: | Ulf Mehlig <umehlig(at)uni-bremen(dot)de> |
---|---|
To: | danla(at)micromotion(dot)com |
Cc: | pgsql-sql(at)postgreSQL(dot)org |
Subject: | Re: [SQL] How match percent sign in SELECT using LIKE? |
Date: | 1999-03-11 08:45:45 |
Message-ID: | 199903110845.JAA02036@pandora3.uni-bremen.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
----------------------------------------------------------------------
From | Date | Subject | |
---|---|---|---|
Next Message | Oleg Broytmann | 1999-03-11 12:48:12 | RE: [HACKERS] Bug on complex subselect (was: Bug on complex join) |
Previous Message | Vikrant Rathore | 1999-03-11 05:59:03 | unsubscribe |