COALESCE and NULL arithmetic

[Former Member]

I need to join strings together with a comma. Should be easy, right?

I have seen techniques in the past involving COALESCE and went for google. I found http://www.sqlteam.com/article/using-coalesce-to-build-comma-delimited-string (the meat of the article is found in the last two paragraphs).

    begin
      DECLARE foo char(50);
      SELECT coalesce(foo+', ', '') + 'bar';
    end;

Expected result: "bar" Actual result: ", bar"

I thought NULL + something would still equal NULL?

(12.01 EBF 3505)

[Former Member]

NULL + something can result in NULL when the + operator is used as arithmetic. In this case, the + operator is being determined to be a concatenation operator. In SQL Anywhere, concatenating a string with a NULL string results in the string. The following page documents properties of NULLs as they relate to SQL Anywhere:

Properties of NULL

Because of this behavior, other options would prove better suited. You could create a function to use in place of coalesce:

CREATE FUNCTION list_coalesce(val LONG VARCHAR, sep LONG VARCHAR, def LONG VARCHAR)
RETURNS LONG VARCHAR
BEGIN
  IF (val IS NOT NULL) THEN RETURN val || sep;
  ELSE RETURN def;
  END IF;
END
go

Or you could try to use a packaged function from SQL Anywhere. The LIST function by default skips over NULL values:

LIST function [Aggregate]

[VolkerBarth]

When the strings are contained in a table, I would also recommend the LIST aggregate and use ', ' as separator.

Otherwise, you might use sa_split_list to turn a longer string text into a list and then use LIST on the result.

In other circumstances (e.g. concatenating different columns), I prefer to use a CASE expression, which might also handle empty strings, such as

select case len(foo) > 0 then foo || ', ' end case || bar as foobar
from myTable

Obviously, I make use here of the fact that the (omitted) ELSE part returns NULL dy default, and concatenation treats NULL as an empty string, as said in these answers.

[Former Member]

String concatenation treats NULL as an empty string. For more details, please see STRING function.

Maybe something like IFNULL function would be of use?

begin
  DECLARE foo char(50);
  SELECT ifnull(foo, '', foo + ',') + 'bar';
end;