Trimming Leading Zeros Takes TRIM LEADING on PostgreSQL and MySQL and LTRIM on SQLite

Order numbers stored as 0042 lose their zeros with TRIM(LEADING '0' FROM code) on PostgreSQL, MySQL and MariaDB. SQLite refuses that form and wants LTRIM(code, '0'), which MySQL and MariaDB refuse in turn.

Standard SQL removes characters other than spaces with TRIM(LEADING, TRAILING or BOTH chars FROM s), which PostgreSQL, MySQL and MariaDB read. SQLite has only TRIM(s, chars), LTRIM(s, chars) and RTRIM(s, chars). PostgreSQL takes those too, while LTRIM and RTRIM on MySQL and MariaDB take one argument.

SELECT TRIM(LEADING '0' FROM '0042'), run on four engines: SQLite 3.45.1 error: near "'0'": syntax error; PostgreSQL 16.15 '42'; MySQL 8.0.46 '42'; MariaDB 10.11.14 '42'.

What Each Engine Answered

The example

SELECT TRIM(LEADING '0' FROM '0042');

gave these answers:

Engine	Answer
SQLite 3.45.1	error: `near "'0'": syntax error`
PostgreSQL 16.15	`'42'`
MySQL 8.0.46	`'42'`
MariaDB 10.11.14	`'42'`

Every Form BareSQL Checks

BareSQL Portable checks 7 forms of this, and each was run on all four engines. yes means the engine ran it and the engines that ran it agreed, no means the engine refused it, and a value is what the engine answered where they disagreed.

Form	SQLite	PostgreSQL	MySQL	MariaDB	What to write instead
`LEADING`	no	yes	yes	yes	SQLite: LTRIM(s, '0').
`TRAILING`	no	yes	yes	yes	SQLite: RTRIM(s, '0').
`BOTH`	no	yes	yes	yes	SQLite: TRIM(s, '0').
`chars FROM`	no	yes	yes	yes	SQLite: TRIM(s, '0').
`TRIM(s, chars)`	yes	yes	no	no	PostgreSQL, MySQL and MariaDB: TRIM(BOTH '0' FROM s).
`LTRIM(s, chars)`	yes	yes	no	no	MySQL and MariaDB: TRIM(LEADING '0' FROM s).
`RTRIM(s, chars)`	yes	yes	no	no	MySQL and MariaDB: TRIM(TRAILING '0' FROM s).

What to Write Instead

To remove spaces, TRIM(s), LTRIM(s) and RTRIM(s) work everywhere. For other characters no form runs on all four engines; trim them in the application, or keep one query per engine.

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: error: TRIM(LEADING ... FROM ...) works on PostgreSQL, MySQL and MariaDB; SQLite refuses it. [trim-from]
    TRIM(LEADING '0' FROM '0042')
    SQLite: LTRIM(s, '0').

1 problem: 1 error.

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