Forum Stats

  • 3,836,791 Users
  • 2,262,191 Discussions
  • 7,900,111 Comments

Discussions

Implementing SUBSTRING and POSITION standard SQL string functions

iwis
iwis Member Posts: 12 Green Ribbon

I 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:

SUBSTRING(s FROM start [FOR length])
POSITION(s2 IN s)

They are already implemented by PostgreSQL and MySQL. I propose to implement these 2 functions in Oracle as well. Currently, 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

Sven W.Shanu Kuar
2 votes

Active · Last Updated

Comments

  • Sven W.
    Sven W. Member Posts: 10,536 Gold Crown

    The last time I checked the ANSI standard allowed each vendor to name the function as they like. But those ISO documents are always difficult to read, so I might be mistaken. From that perspective Oracle is ANSI compliant. However having an ALIAS and a slightly different syntax, would not hurt as long as the old syntax still works.