Thread: How match percent sign in SELECT using LIKE?
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 '#'; However, PostgreSQL doesn't appear to support the ESCAPE keyword. Any suggestions would be greatly appreciated. I'm using PostgreSQL v6.2.1. Thanks, Dan Lauterbach
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
----------------------------------------------------------------------
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
At 17:45 +0200 on 15/3/99, Bruce Momjian wrote:
> I have overhauled the LIKE code. %% is not a literal %, but is the same
> as wildcard %. Literal % is \%.
This is not SQL 92 compliant, is it? The standard states that if you don't
have an ESCAPE part to the like (i.e. abc LIKE 'bla\%%' ESCAPE '\'), then
there is no escape. I think the correct thing to do is to support the
ESCAPE clause:
- - - begin quotation - - -
Format
<like predicate> ::=
<match value> [ NOT ] LIKE <pattern>
[ ESCAPE <escape character> ]
<match value> ::= <character value expression>
<pattern> ::= <character value expression>
<escape character> ::= <character value expression>
[snip]
a) If an <escape character> is specified, then:
i) If the length in characters of E is not equal to 1, then
an exception condition is raised: data exception-invalid
escape character.
ii) If there is not a partitioning of the string P into sub-
strings such that each substring has length 1 or 2, no
substring of length 1 is the escape character E, and each
substring of length 2 is the escape character E followed by
either the escape character E, an <underscore> character,
or the <percent> character, then an exception condition is
raised: data exception-invalid escape sequence.
If there is such a partitioning of P, then in that parti-
tioning, each substring with length 2 represents a single
occurrence of the second character of that substring. Each
substring with length 1 that is the <underscore> character
represents an arbitrary character specifier. Each substring
with length 1 that is the <percent> character represents
an arbitrary string specifier. Each substring with length
1 that is neither the <underscore> character nor the <per-
cent> character represents the character that it contains.
b) If an <escape character> is not specified, then each <under-
score> character in P represents an arbitrary character spec-
ifier, each <percent> character in P represents an arbitrary
string specifier, and each character in P that is neither the
<underscore> character nor the <percent> character represents
itself.
- - - end quotation - - -
Herouth
--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma
Yes, you are correct. However, since '\' is an escape in all our
strings, it makes sense to use it as the default escape in LIKE.
I agree we should allow ESC, but that would make LIKE a trinary
operation, rather than a binary. If you want really confusing, the code
for LIKE really does:
| a_expr LIKE a_expr
{ $$ = makeIndexable("~~", $1, $3); }
so it maps LIKE to a binary operator "~~". How do we map that into a
trinary operator, which we don't support? Doesn't really seem worth it.
I can add an item to the TODO list if you wish?
> At 17:45 +0200 on 15/3/99, Bruce Momjian wrote:
>
>
> > I have overhauled the LIKE code. %% is not a literal %, but is the same
> > as wildcard %. Literal % is \%.
>
> This is not SQL 92 compliant, is it? The standard states that if you don't
> have an ESCAPE part to the like (i.e. abc LIKE 'bla\%%' ESCAPE '\'), then
> there is no escape. I think the correct thing to do is to support the
> ESCAPE clause:
>
> - - - begin quotation - - -
>
> Format
>
> <like predicate> ::=
> <match value> [ NOT ] LIKE <pattern>
> [ ESCAPE <escape character> ]
>
> <match value> ::= <character value expression>
>
> <pattern> ::= <character value expression>
>
> <escape character> ::= <character value expression>
>
> [snip]
>
> a) If an <escape character> is specified, then:
>
> i) If the length in characters of E is not equal to 1, then
> an exception condition is raised: data exception-invalid
> escape character.
>
> ii) If there is not a partitioning of the string P into sub-
> strings such that each substring has length 1 or 2, no
> substring of length 1 is the escape character E, and each
> substring of length 2 is the escape character E followed by
> either the escape character E, an <underscore> character,
> or the <percent> character, then an exception condition is
> raised: data exception-invalid escape sequence.
>
> If there is such a partitioning of P, then in that parti-
> tioning, each substring with length 2 represents a single
> occurrence of the second character of that substring. Each
> substring with length 1 that is the <underscore> character
> represents an arbitrary character specifier. Each substring
> with length 1 that is the <percent> character represents
> an arbitrary string specifier. Each substring with length
> 1 that is neither the <underscore> character nor the <per-
> cent> character represents the character that it contains.
>
> b) If an <escape character> is not specified, then each <under-
> score> character in P represents an arbitrary character spec-
> ifier, each <percent> character in P represents an arbitrary
> string specifier, and each character in P that is neither the
> <underscore> character nor the <percent> character represents
> itself.
>
> - - - end quotation - - -
>
> Herouth
>
> --
> Herouth Maoz, Internet developer.
> Open University of Israel - Telem project
> http://telem.openu.ac.il/~herutma
>
>
>
--
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
Bruce Momjian wrote:
> I agree we should allow ESC, but that would make LIKE a trinary
> operation, rather than a binary. If you want really confusing, the code
> for LIKE really does:
>
> | a_expr LIKE a_expr
> { $$ = makeIndexable("~~", $1, $3); }
>
> so it maps LIKE to a binary operator "~~". How do we map that into a
> trinary operator, which we don't support? Doesn't really seem worth it.
>
> I can add an item to the TODO list if you wish?
One useful and probably not too hard thing to do is to allow ESCAPE '\' on
the end of the LIKE clause. Any character other than '\' will be an error.
This allows Postgres users to write compliant SQL code that can be used
with other databases.
Another approach is to "rewrite" the match string at parse time. If it is a
known constant, you can do the whole job there. Otherwise, you'd insert an
extra node in the parse tree which does the rewrite just before calling hte
"~~" operator. (I am assuming the match string can be a general expression
and that you can add a function of two arguments which rewrites the first
argument using the second argument as the escape character. This is of
course not the utmost of micro efficiency, but I doubt it would matter
much.)
But I don't have in depth knowledge of the Postgres SQL parser and
evaluator so I may be way off base.
-Z-
> One useful and probably not too hard thing to do is to allow ESCAPE '\' on > the end of the LIKE clause. Any character other than '\' will be an error. > This allows Postgres users to write compliant SQL code that can be used > with other databases. This is an excellent idea, I will implement it. > > Another approach is to "rewrite" the match string at parse time. If it is a > known constant, you can do the whole job there. Otherwise, you'd insert an > extra node in the parse tree which does the rewrite just before calling hte > "~~" operator. (I am assuming the match string can be a general expression > and that you can add a function of two arguments which rewrites the first > argument using the second argument as the escape character. This is of > course not the utmost of micro efficiency, but I doubt it would matter > much.) > > But I don't have in depth knowledge of the Postgres SQL parser and > evaluator so I may be way off base. That is also an excellent idea. Just convert their escape to \ inside the parser. Of course, they still have to use \\ to get a \, as in any string. Great idea. -- 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
Bruce Momjian wrote: > That is also an excellent idea. Just convert their escape to \ inside > the parser. Of course, they still have to use \\ to get a \, as in any > string. Great idea. You can even make it fully compliant if you want. (There are of course backward compatibility problems. I'm not sure what the Postgres policy is on this.) - If the escape character is backslash, do nothing. - Otherwise, turn all backslashes in the string to double backslashes. - If the escape character is not set, stop here. - Turn all occurences of the escape character into a backslash except where the escape character is doubled, where it should be made into a single occurence. (Optionally, if "\n" is just an 'n' character, you can handle double occurences of the escape character by turning the first one into a backslash.) Probably the best bet for PostgreSQL programmers is to always code Like clauses with an ESCAPE '\' (or however its written). I really wish they'd chosen a character other than underscore for the "match one" wildcard... Is there any standard practice for seperating words in table names? -Z-
> Bruce Momjian wrote: > > That is also an excellent idea. Just convert their escape to \ inside > > the parser. Of course, they still have to use \\ to get a \, as in any > > string. Great idea. > > You can even make it fully compliant if you want. (There are of course > backward compatibility problems. I'm not sure what the Postgres policy is > on this.) > > - If the escape character is backslash, do nothing. > - Otherwise, turn all backslashes in the string to double backslashes. Wow, there are great ideas. No wonder commercial database have problems. We have all this ideas coming from so many people. > - If the escape character is not set, stop here. > - Turn all occurences of the escape character into a backslash except > where the escape character is doubled, where it should be made into a > single occurence. > (Optionally, if "\n" is just an 'n' character, you can handle double > occurences of the escape character by turning the first one into a > backslash.) > > Probably the best bet for PostgreSQL programmers is to always code Like > clauses with an ESCAPE '\' (or however its written). > > I really wish they'd chosen a character other than underscore for the > "match one" wildcard... Is there any standard practice for seperating words > in table names? Yes, it is a bad choice. -- 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
At 00:26 +0200 on 17/03/1999, Bruce Momjian wrote: > > > > But I don't have in depth knowledge of the Postgres SQL parser and > > evaluator so I may be way off base. > > That is also an excellent idea. Just convert their escape to \ inside > the parser. Of course, they still have to use \\ to get a \, as in any > string. Great idea. Just a quick note about compliance: It has to work two ways. That is, if someone imports an SQL script from his previous database (or don't you think people will be migrating *to* postgres, only *from* it?), it has to work. Thus, if someone has a script written to the SQL92 standard, he is allowed to use a construct like ...WHERE pathname LIKE 'C:\WINDOWS\%' Without encountering oddities. Herouth -- Herouth Maoz, Internet developer. Open University of Israel - Telem project http://telem.openu.ac.il/~herutma
> Thus, if someone has a script written to the SQL92 standard, he is allowed > to use a construct like > > ...WHERE pathname LIKE 'C:\WINDOWS\%' > > Without encountering oddities. Yes, but all our strings us '\' as escape. If we turn it off just for the LIKE string, people will be very confused. -- 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
Good ideas. Added to TODO list.
> Bruce Momjian wrote:
> > I agree we should allow ESC, but that would make LIKE a trinary
> > operation, rather than a binary. If you want really confusing, the code
> > for LIKE really does:
> >
> > | a_expr LIKE a_expr
> > { $$ = makeIndexable("~~", $1, $3); }
> >
> > so it maps LIKE to a binary operator "~~". How do we map that into a
> > trinary operator, which we don't support? Doesn't really seem worth it.
> >
> > I can add an item to the TODO list if you wish?
>
> One useful and probably not too hard thing to do is to allow ESCAPE '\' on
> the end of the LIKE clause. Any character other than '\' will be an error.
> This allows Postgres users to write compliant SQL code that can be used
> with other databases.
>
> Another approach is to "rewrite" the match string at parse time. If it is a
> known constant, you can do the whole job there. Otherwise, you'd insert an
> extra node in the parse tree which does the rewrite just before calling hte
> "~~" operator. (I am assuming the match string can be a general expression
> and that you can add a function of two arguments which rewrites the first
> argument using the second argument as the escape character. This is of
> course not the utmost of micro efficiency, but I doubt it would matter
> much.)
>
> But I don't have in depth knowledge of the Postgres SQL parser and
> evaluator so I may be way off base.
>
> -Z-
>
-- 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,
Pennsylvania19026
I have added this to the TODO list. It is a good idea. > Bruce Momjian wrote: > > That is also an excellent idea. Just convert their escape to \ inside > > the parser. Of course, they still have to use \\ to get a \, as in any > > string. Great idea. > > You can even make it fully compliant if you want. (There are of course > backward compatibility problems. I'm not sure what the Postgres policy is > on this.) > > - If the escape character is backslash, do nothing. > - Otherwise, turn all backslashes in the string to double backslashes. > - If the escape character is not set, stop here. > - Turn all occurences of the escape character into a backslash except > where the escape character is doubled, where it should be made into a > single occurence. > (Optionally, if "\n" is just an 'n' character, you can handle double > occurences of the escape character by turning the first one into a > backslash.) > > Probably the best bet for PostgreSQL programmers is to always code Like > clauses with an ESCAPE '\' (or however its written). > > I really wish they'd chosen a character other than underscore for the > "match one" wildcard... Is there any standard practice for seperating words > in table names? > > -Z- > -- 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, Pennsylvania19026
Here are the TODO items:* Allow ESCAPE '\' at the end of LIKE for ANSI compliance* Rewrite the LIKE handling by rewritingthe user string with the supplied ESCAPE > Bruce Momjian wrote: > > That is also an excellent idea. Just convert their escape to \ inside > > the parser. Of course, they still have to use \\ to get a \, as in any > > string. Great idea. > > You can even make it fully compliant if you want. (There are of course > backward compatibility problems. I'm not sure what the Postgres policy is > on this.) > > - If the escape character is backslash, do nothing. > - Otherwise, turn all backslashes in the string to double backslashes. > - If the escape character is not set, stop here. > - Turn all occurences of the escape character into a backslash except > where the escape character is doubled, where it should be made into a > single occurence. > (Optionally, if "\n" is just an 'n' character, you can handle double > occurences of the escape character by turning the first one into a > backslash.) > > Probably the best bet for PostgreSQL programmers is to always code Like > clauses with an ESCAPE '\' (or however its written). > > I really wish they'd chosen a character other than underscore for the > "match one" wildcard... Is there any standard practice for seperating words > in table names? > > -Z- > -- 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, Pennsylvania19026