SQLite Forum

Proposal: Add the SUBSTRING alias to the SUBSTR function
Login

Proposal: Add the SUBSTRING alias to the SUBSTR function

(1.4) By tarest on 2021-04-15 10:13:56 edited from 1.3 [link]

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](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

You will be glad to see this change appears as item 10 in the [SQLite Release 3.34.0 change notes](https://sqlite.org/draft/releaselog/3_34_0.html).

(3) By tarest on 2020-11-24 15:35:03 in reply to 2 [link]

Nice! Thank you.

(4) By Holger J (holgerj) on 2020-11-26 12:13:20 in reply to 1.1 [link]

PostgreSQL also supports the ANSI/ISO standard version. It's a bit verbose, but standard is standard.

(6.1) By tarest on 2021-03-20 17:16:58 edited from 6.0 in reply to 4 [link]

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](https://github.com/iwis/SQL-notes/blob/main/Functions.txt)

(5) By John Dennis (jdennis) on 2020-11-27 01:28:48 in reply to 1.1 [link]

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.

(7) By tarest on 2020-11-27 18:09:12 in reply to 5 [link]

I checked it in [the Ingres documentation](https://docs.actian.com/ingres/10s/index.html#page/SQLRef/String_Functions.htm#ww556302) - it supports: `SUBSTR(s, start [,length])` and `SUBSTRING(s FROM start [FOR length])` from the SQL Standard.

(8) By John Dennis (jdennis) on 2020-11-29 02:42:30 in reply to 7 [link]

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.