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)
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:
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:
answered 05 Dec '11, 13:44
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;