SUBSTR Functionality Differences Between Oracle and PostgreSQL: What You Need to Know

Migrating away from Oracle can be both fun and challenging. One of the core functionality you may encounter while moving away from Oracle is the usage of string-related functions, such as SUBSTR, INSTR, and CONCAT. These functions are widely used in the Oracle ecosystem, but they may not be compatible with open-source options like PostgreSQL.

While similar functions may be available in PostgreSQL, they may differ in functionality and input argument support.

In this blog, we will explore the differences between the SUBSTR function in Oracle and PostgreSQL when migrating away from Oracle. It is important to consider the impact of string manipulation functions, as they are often embedded as part of application SQL and can affect the overall migration pattern for both the database and the application logic.

Oracle substr vs PostgreSQL substr

It’s critical to understand difference between substr implementation on the database engine. Below tabular representation gives a comparative view.

Query	Oracle output	PostgreSQL output
substr(‘abcdefgh’,0,1)	a	” (Empty String)
substr(‘abcdefgh’,-3,1)	f	” (Empty String)
substr(‘abcdefgh’,-10,1)	NULL	” (Empty String)
substr(‘abcdefgh’,1,-1)	NULL	ERROR: negative substring length not allowed
substr(‘abcdefgh’,-8,4)	abcd	” (Empty String)
substr(‘abcdefgh’,10,1)	NULL	” (Empty String)

Oracle vs PostgreSQL – Substr function

let’s break it down to note the core difference.

• In Oracle, if the start position argument is 0, it is treated as 1, which creates a difference compared to PostgreSQL.
• Oracle supports negative start position arguments, but PostgreSQL will always return an empty string.
• PostgreSQL will throw an exception if the substring length is negative, while Oracle will return NULL.
• If the negative start position argument has a value greater than the string length, Oracle will return NULL.

Now, it is fair to say that substr functions in PostgreSQL is not directly compatible with Oracle. so what option we have to have compatible option when migrating away from Oracle.

Orafce extension provides inbuilt wrapper with functionality compatible with Oracle substr.

orafce – substr functionality

let’s re-validate the initial tabular comparision with Orafce Extension – Substr

Query	Oracle output	Orafce – Substr
substr(‘abcdefgh’,0,1)	a	a
substr(‘abcdefgh’,-3,1)	f	f
substr(‘abcdefgh’,-10,1)	NULL	” (Empty String)
substr(‘abcdefgh’,1,-1)	NULL	NULL
substr(‘abcdefgh’,-8,4)	abcd	abcd
substr(‘abcdefgh’,10,1)	NULL	” (Empty String)

Oracle vs Orafce – Substr function

Orafce extension provides a substr wrapper within oracle schema that is mostly compatible but not fully. Empty string and NULL are two different values in PostgreSQL, if interested in more insight do check out blog in the tweet.

If you think coalesce is best alternative on replacing nvl function when migrating from #Oracle to #PostgreSQL

Please do check out for #weekend https://t.co/I9MLfukiuV

— Deepak Mahto (@mahtodeepak05) January 6, 2023

Mitigation on resolving difference between Oracle and PostgreSQL on returning empty string and NULL will involve creating additional wrapper function in User Schema and use it as replacement on every occurrence of SUBSTR.

CREATE FUNCTION user1.substr(str text, start integer, len integer) RETURNS text
    LANGUAGE sql IMMUTABLE PARALLEL SAFE
    AS $_$
    SELECT nullif(oracle.substr($1,$2,$3),'');

We can also use NULLIF on every occurrence of SUBSTR as migration pattern.

Query	Oracle output	Wrapper – user1.substr
substr(‘abcdefgh’,0,1)	a	a
substr(‘abcdefgh’,-3,1)	f	f
substr(‘abcdefgh’,-10,1)	NULL	NULL
substr(‘abcdefgh’,1,-1)	NULL	NULL
substr(‘abcdefgh’,-8,4)	abcd	abcd
substr(‘abcdefgh’,10,1)	NULL	NULL

Oracle vs Wrapper – Substr function

Overwriting pg_catalog is not supported as default, we will need to change the search path to use wrapper code wither in oracle schema provided by Orafce or custom wrapper on top of Orafce.

set search_path =user1,pg_catalog,public;

select substr('abcdefgh',-3,1), pg_catalog.substr('abcdefgh',-3,1);

Orafce ease out overall need of compatible functions from Oracle and in case need arise we can overrite it by creating wrapper functions in users schema.