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:
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.
asked 10 Oct '14, 03:43
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:
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:
answered 15 Oct '14, 13:18
Ivan T. Bowman
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:
That will return
Apparently, one could also use the string NULL to be replaced with a NULL value.
answered 10 Oct '14, 03:54