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