There will be no singularity: “SQL-WTF SQL-TIL It would seem that what can happen with escaping chara…”

~~SQL-WTF~~ SQL-TIL

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
ORDER BY s;

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.....