How to make sa_split_list() return a NULL value?

[#1]

This is a question based on that one...

The system procedure sa_split_list() is helpful to generate result sets from a simple list of values. However, how can a NULL value be generated in such a list?

Specifying NULL or just an empty value within the list of values will not do:

select *, if row_value is null then 1 else 0 end if as null_value
from sa_split_list('1,,NULL,4');


will return
line_num  row_value null_value
1         1         0
2                   0
3         NULL      0
4         4         0


That's not really unexpected as the procedure's parameter is treated as a list of string values, and '' is an empty string and 'NULL' a string value. 

[VolkerBarth]

The solution seems to require a small wrapper around that procedure's result set by using an if expression to replace a particular string value with a NULL value.

Here the empty value is used for that:

select line_num, if row_value <> '' then row_value else null end if as row_value
from sa_split_list('1,,NULL,4');


That will return
line_num  row_value
1         1         
2         (null)    <- that's a NULL value
3         NULL      <- that's the string 'NULL'
4         4


Apparently, one could also use the string NULL to be replaced with a NULL value.