null handling in substr built in function

Database

null handling in substr built in function

I have used the below select statement in Teradata and Oracle and i get different answers

SELECT SUBSTR('*',2,4) FROM DUAL;----IN ORACLE THIS GIVES NULL AS A RESULT

SELECT SUBSTR('*',2,4) ;----IN TERADATA THIS DOES NOT GIVES NULL AS A RESULT

Could anyone please explain this?
3 REPLIES
Enthusiast

Re: null handling in substr built in function

Hi Ravi,
In Teradata, SUBSTR function returns a zero length string if start position for extraction is greater than string length and length of extraction is greater than 0.

Also, In Teradata, SUBSTR is a Teradata extension to the ANSI SQL-2003 standard. The same goes for Oracle. Hence the difference in the result. I'm not sure if SUBSTRING function is available on Oracle, though it's available on Teradata and is ANSI SQL-2003 standard compliant.

Hope this helps. Thanks!

Re: null handling in substr built in function

Thanks Sushil for the prompt reply.

This means that while migrating PLSQL procedures from Oracle to teradata we need to make changes for this in the logic as the handling for the above scenario is different in both the databases.
Senior Apprentice

Re: null handling in substr built in function

Oracle does not distinguish between a zero-length VARCHAR string and NULL, this is a well-known Oracle bug/feature :-)

And Oracle's SUBSTR has different rules, e.g. for negative start positions.

Btw, another function with different rules is ADD_MONTHS.

Dieter