SQL: escaping with backslash

user_2460378 0
Viewed 160

I'm writing a lexer for SQL for syntax coloring and have the problem of parsing code like

SELECT * FROM table t WHERE t.name LIKE 'FOO\_%' ESCAPE '\';

Is my understanding correct, that \ escapes some characters like underscore (\_), but not if it is followed by a single quote (\')? Does the escaping depends on the SQL dialect or is the behavior the same for all (or a lot of) dialects?

Is it correct, that '\' usually would mean an incomplete string literal, because the backslash escapes the single quote?

syntax sql
1 Answers

By default the \ doesn't really have any special meaning in a string/character constant in SQL. Single quotes are "escaped" by doubling them in SQL e.g. 'Arthur''s house'

The only situation where it does have a special meaning is the ESCAPE clause. The default escape character to escape wildcards for SQL LIKE is the \ but it can be changed through the ESCAPE clause.

So the following conditions are identical:

t.name LIKE 'FOO\_%'
t.name LIKE 'FOO\_%' ESCAPE '\'
t.name LIKE 'FOO#_%' ESCAPE '#'

But for strings that are not used for a LIKE condition, the backslash has no special meaning in "SQL", i.e. in ANSI standard SQL.

Some database products however do support non-standard escaping of characters using \ inside regular strings. Postgres used to do that, but this was deprecated a long time, and the default behaviour today is, that a \ does not escape anything in a string constant, so '\t' is simply a backslash character followed by a t. It can be configured to accepe non-standard strings where that would result in the "tab character" instead.

I think MySQL always uses this non-standard behaviour of a backslash inside a string constant.

user_3303150
Related