Proposal: Add the SUBSTRING alias to the SUBSTR function
This is the current syntax of the
SUBSTR[ING] function in ANSI standard and in different RDBMS:
ANSI standard: SUBSTRING(s FROM start [FOR length]) PostgreSQL & MySQL: SUBSTR[ING](s, start [,length]) Oracle & SQLite: SUBSTR (s, start [,length]) SQL Server: SUBSTRING (s, start ,length )
To unify the syntax, I propose to add the
SUBSTRING alias to the
SUBSTR function. If Oracle does the same, we will be able to use the
SUBSTRING(s, start, length) syntax in all above-mentioned RDBMS.
Changes proposed by me in other RDBMS: https://github.com/iwis/SQL-notes/blob/master/Functions_proposed_changes.md
(2) By Larry Brasfield (LarryBrasfield) on 2020-11-23 20:25:58 in reply to 1.1 [link] [source]
You will be glad to see this change appears as item 10 in the SQLite Release 3.34.0 change notes.
PostgreSQL also supports the ANSI/ISO standard version. It's a bit verbose, but standard is standard.
In addition to the
SUBSTRING(s FROM start [FOR length]) function, SQLite implements the following string functions differently than the SQL standard:
POSITION(s2 IN s), TRIM([[LEADING|TRAILING|BOTH] [c] FROM] s), CHAR[ACTER]_LENGTH(s), OCTET_LENGTH(s).
I don't know if implementing them in SQLite is easy. However, this would only slightly increase portability between different RDBMS. The problem is that Oracle and SQL Server don't implement them (with one exception): https://github.com/iwis/SQL-notes/blob/main/Functions.txt
For what it's worth Ingres (now Actian Ingres, I think) follows PostgreSQL and MySQL - both SUBSTR and SUBSTRING are supported. When I worked with and for Ingres we were stuck with LEFT and RIGHT so a substring got a little messy.
I checked it in the Ingres documentation - it supports:
SUBSTR(s, start [,length]) and
SUBSTRING(s FROM start [FOR length]) from the SQL Standard.
I checked it by running the queries. Both the standard SUBSTRING (s FROM start [FOR length]) and the variant SUBSTRING(s,start[,length]) work, and return identical rows. I would call it a documentation bug, or if you like, an undocumented feature :-) This is in 10.2, I don't know what 10S reports as a version string.