The forum will be down for scheduled maintenance on Saturday, March 4 beginning at 10am EST. Actual downtime is unknown but may be several hours.

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)

asked 05 Dec '11, 13:18

Rune's gravatar image

Rune
2365817
accept rate: 50%

edited 05 Dec '11, 13:20


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]

permanent link

answered 05 Dec '11, 13:44

Tyson%20Lewis's gravatar image

Tyson Lewis
2.2k1641
accept rate: 22%

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;
permanent link

answered 05 Dec '11, 13:39

Elmi%20Eflov's gravatar image

Elmi Eflov
7811014
accept rate: 31%

edited 05 Dec '11, 13:43

Your answer
toggle preview

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "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:

×90

question asked: 05 Dec '11, 13:18

question was seen: 1,852 times

last updated: 06 Dec '11, 15:41