Special Character Escaping Behavior

It would seem that what can happen with escaping characters in strings? Everything has been known for a long time and works the same everywhere…

But what happens if you escape characters that don't need to be escaped?

CREATE TABLE vals(s VARCHAR(5));
INSERT INTO vals(s) VALUES('%');
INSERT INTO vals(s) VALUES('\%');
INSERT INTO vals(s) VALUES('\\%');

SELECT
    s,
    s LIKE '%' as "%",
    s LIKE '\%' as "\%",
    s LIKE '\\%' as "\\%"
FROM vals;

Now let's run in different databases. Note the values of S.

Snowflake:

| S  | %    | \%   | \\%   |
|----|------|------|-------|
| %  | TRUE | TRUE | FALSE |
| %  | TRUE | TRUE | FALSE |
| \% | TRUE | TRUE | TRUE  |

Sqlite:

| s   | % | \% | \\% |
|-----|---|----|-----|
| %   | 1 | 0  | 0   |
| \%  | 1 | 1  | 0   |
| \\% | 1 | 1  | 1   |

Mysql/MariaDB/Clickhouse:

| s  | % | \% | \% |
|----|---|----|----|
| %  | 1 | 1  | 1  |
| \% | 1 | 0  | 0  |
| \% | 1 | 0  | 0  |

PostgreSQL:

| s   | %    | \%    | \\%   |
|-----|------|-------|-------|
| %   | true | true  | false |
| \%  | true | false | true  |
| \\% | true | false | true  |

Oracle, BigQuery, Databricks and SQL Server don't support this syntax.

So, well… Good thing at least '%' works the same everywhere…..