This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 30th, when you will be able to use this site as normal.

    Forum Stats

  • 3,890,115 Users
  • 2,269,775 Discussions
  • 7,916,823 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. GermanyMember Posts: 10,562 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.