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.

asked 10 Oct '14, 03:43

Volker%20Barth's gravatar image

Volker Barth
accept rate: 32%

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')
permanent link

answered 15 Oct '14, 13:18

Ivan%20T.%20Bowman's gravatar image

Ivan T. Bowman
accept rate: 39%

As usual: I simply had an answer but Ivan does really know all facts:). Thank you so much!

And apparently even the standard NULLIF() function has not yet been noticed by me:(

(16 Oct '14, 03:45) Volker Barth
Replies hidden

Same with me, still learning ...

(17 Oct '14, 03:27) Reimer Pods

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.

permanent link

answered 10 Oct '14, 03:54

Volker%20Barth's gravatar image

Volker Barth
accept rate: 32%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 10 Oct '14, 03:43

question was seen: 647 times

last updated: 17 Oct '14, 03:27