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

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