SAP SQL Anywhere

Question

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.

Accepted Answer

The OPENSTRING syntax offers more flexibility in terms of data types and load formats and is in general more efficient for large inputs. It looks like your data contains integers, so you could do the following:

select *
from openstring( value '1,,4' )
    with( a integer )
    options( delimited by '\0' row delimited by ',') D

If your input is instead strings, you can still use OPENSTRING but you need to decide how to represent empty strings and null values. The option clause lets you describe whether quotes are expected and so on. By default, an empty string would be treated as NULL while '' would be treated as an empty string.

The UNLOAD SELECT ... INTO VARIABLE statement can be used to generate strings that can be read by OPENSTRING. This approach is also more general in that you can support multiple columns.

In version 16, the sp_parse_json procedure can also be used to parse text into a variable containing arrays or rows (based on the JSON in the string). The FOR JSON clause can also be used to generate strings from a SELECT statement (even if the select contains an ARRAY and/or ROW type). These are more convenient for dealing with more complex data structures not easily represented as a single flat table. The XML support can also be used in some cases.

The sa_split_list() procedure is a really handy procedure with a simple interface but it doesn't give a lot of control. The performance also is not as good as other methods because the list is split into a temporary table while OPENSTRING returns rows directly to the query without an intervening copy step.

I just wanted to add that Volker's answer is a good one when using sa_split_list(); another way to write the IF expression is as:

select line_num, NULLIF(row_value,'')
from sa_split_list('1,,NULL,4')

Answer 2

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.

SAP SQL Anywhere

How to make sa_split_list() return a NULL value?

Follow this question

Related questions