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

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-