A Backslash in LIKE Escapes Percent and Underscore Everywhere Except SQLite

Searching for the text 50_off with LIKE '50\_off' works on PostgreSQL, MySQL and MariaDB, where the backslash escapes the underscore. SQLite treats the backslash as an ordinary character, and the search finds nothing.

'a_b' LIKE 'a\_b' is true on PostgreSQL, MySQL and MariaDB, where \_ matches an underscore, and false on SQLite, where the pattern asks for a backslash followed by any one character.

What Each Engine Answered

The example

SELECT 'a_b' LIKE 'a\_b';

gave these answers:

What to Write Instead

This form answers the same on every engine:

SELECT 'a_b' LIKE 'a!_b' ESCAPE '!';

Name the escape character: LIKE 'a!_b' ESCAPE '!' means the same on all four.

What BareSQL Portable Reports

Saved as example.sql, the example gets this from baresql lint --all:

$ baresql lint --all example.sql
example.sql:1:8: info: LIKE ignores letter case on SQLite, MySQL and MariaDB and respects it on PostgreSQL. [like-case]
    'a_b' LIKE 'a\_b'
    Write LOWER(x) LIKE LOWER(pattern), or a pattern that is already in lower case, to ignore case on all four.
example.sql:1:14: warning: Without an ESCAPE clause, a backslash escapes % and _ in LIKE on PostgreSQL, MySQL and MariaDB; on SQLite it is an ordinary character. [like-escape]
    LIKE 'a\_b'
    Name the escape character: LIKE 'a!_b' ESCAPE '!' means the same on all four.

2 problems: 1 warning, 1 note.

The example also runs into LIKE and letter case, which has a post of its own.

baresql rules like-escape prints the same answers in the terminal, and the BareSQL Portable page lists every difference BareSQL knows.