How can a substring from a varchar variable be null?

Question

First of all, as a SQL Server user, I was surprised that this syntax is valid in Oracle:

select var from table where substr(var, 2, 1) is null 

How can a substring from a varchar string variable be null ? In SQL Server, this will never be true (?).

 select var from table where substring(var, 2, 1) is null 

Answer 1

In many cases, in Oracle, an empty string is actually NULL .

If substr () is outside the range of your string, SQL returns an empty string, and Oracle returns NULL (equivalent to an empty string).

Answer 2

In general, null means no value.

 select substr ('abcd',2, 1) from dual; -- result: 'b' select substr ('a',2, 1) from dual; -- empty, because there is nothing at second position on string 'a'; select decode(substr('a',2,1),null, 'yes, is null') from dual; -- result: 'yes, is null' select decode('', null, 'yes, is null') from dual; -- result: 'yes, is null' 

Update: Clearly, the choice of implementation. In most languages, for example, in Java, strings are placed in a memory region, and for a programmer, string variables are references to a region in memory. Thus, an empty string is a reference to a region of length 0, but this reference is not null. So, null is a different matter (no link), and I think you are: better.