|
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: 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.
(06 Dec '11, 15:39)
Volker Barth
|
|
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; |
