SUBSTRING With FROM and FOR Fails on SQLite

SUBSTRING(phone FROM 1 FOR 3) reads like plain English and runs on PostgreSQL, MySQL and MariaDB. SQLite stops at FROM with a syntax error.

The FROM ... FOR form is standard SQL, and PostgreSQL, MySQL and MariaDB read it. SQLite takes only the form with commas, SUBSTR(s, start, length) or SUBSTRING(s, start, length). PostgreSQL also takes FOR without FROM, which MySQL and MariaDB refuse, and reads a text after FROM as a regular expression: SUBSTRING(s FROM '[0-9]+') is the first run of digits there and an empty string on MySQL and MariaDB.

What Each Engine Answered

The example

SELECT SUBSTRING('abcdef' FROM 2 FOR 3);

gave these answers:

Every Form BareSQL Checks

BareSQL Portable checks 4 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.

What to Write Instead

This form answers the same on every engine:

SELECT SUBSTR('abcdef', 2, 3);

Write SUBSTR(s, start, length), counting from 1 (see SUBSTR from position 0 or below). For a pattern, use REGEXP_SUBSTR on MySQL and MariaDB and SUBSTRING ... FROM on PostgreSQL; SQLite has no built-in way.

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: SUBSTRING(s FROM a FOR b) works on PostgreSQL, MySQL and MariaDB; SQLite refuses it. [substring-from]
    SUBSTRING('abcdef' FROM 2 FOR 3)
    Write SUBSTR(s, 2, 3).

1 problem: 1 error.

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