Skip site navigation (1) Skip section navigation (2)

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

From: Bruce Momjian <maillist(at)candle(dot)pha(dot)pa(dot)us>
To: herouth(at)oumail(dot)openu(dot)ac(dot)il (Herouth Maoz)
Cc: pgsql-sql(at)postgreSQL(dot)org
Subject: Re: [SQL] How match percent sign in SELECT using LIKE?
Date: 1999-03-16 20:24:50
Message-ID: 199903162024.PAA18319@candle.pha.pa.us (view raw or flat)
Thread:
Lists: pgsql-sql
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(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

In response to

Responses

pgsql-sql by date

Next:From: Zalman SternDate: 1999-03-16 20:51:41
Subject: Re: [SQL] How match percent sign in SELECT using LIKE?
Previous:From: Tim PerdueDate: 1999-03-16 14:17:55
Subject: Re: [SQL] Setting Next Value in Sequence

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group