SQLite Forum

Proposal: Implementing TRIM, SUBSTRING and POSITION standard SQL string functions
Login

Proposal: Implementing TRIM, SUBSTRING and POSITION standard SQL string functions

(1.3) By tarest on 2021-04-15 10:15:02 edited from 1.2 [source]

I have compared string functions in 5 different RDBMS: PostgreSQL, MySQL, Oracle, SQL Server and SQLite, and also in the ANSI SQL Standard. There are large differences among different RDBMS, and I propose to reduce them a little bit.

The SQL Standard defines, among others, these functions operating on text strings:

  TRIM([[LEADING|TRAILING|BOTH] [c] FROM] s)
  SUBSTRING(s FROM start [FOR length])
  POSITION(s2 IN s)

They are already implemented by PostgreSQL and MySQL. Oracle implements only TRIM function according to SQL Standard.

I propose to implement these 3 functions in Oracle, SQL Server and SQLite as well. However, I don't know if the support of the LEADING, TRAILING, BOTH, FROM, FOR, IN keywords in the list of function parameters will not be a problem in SQLite.

Currently, SQL Server implements the following functions that work identically to the SQL Standard:

  [L|R]TRIM([c FROM] s)
  SUBSTRING(s, start, length)
  CHARINDEX(s2, s)

SQLite implements the following functions that work identically to the SQL Standard:

  [L|R]TRIM(s [,c])
  SUBSTR(s, start [,length])
  INSTR(s, s2)

and Oracle implements the following functions that work identically to the SQL Standard:

  SUBSTR(s, start [,length])
  INSTR(s, s2)

For more information about comparing implementations of the scalar functions, click here: https://github.com/iwis/SQL-notes/blob/master/Functions.txt