Ask Reuben

Append (,) or Concatenate (||)

What should I use to join two or more strings together? Append (,) or Concatenate (||) ?

A question I have been asked a few times, is it better to use append (,) or concatenate (||) to join two strings together?

DEFINE s1, s2, s3 STRING
-- What is better, using append
LET s3 = s1, s2
-- or concatenation
LET s3 = s1 || s2

The quick answer is what result do you expect if one of the operands is NULL.  With concatenation (||), if one of the operands is NULL then the result will be NULL as well.  So for a quick test, note difference in output of …

LET s1 = "foo"
DISPLAY s1, s2      -- outputs "foo"
DISPLAY s1 || s2    -- outputs NULL

However the answer I like to give back is that are you aware that there is a 3rd option?, that is the SFMT operator.

DEFINE s1, s2, s3 STRING 
-- What is better, 
LET s3 = s1, s2 
-- or concatenation 
LET s3 = s1 || s2 
-- what about a 3rd option, SFMT 
LET s3 = SFMT("%1%2", s1, s2)

This was introduced in 1.20 so has been available for a number of years. Like append (,) it won’t return NULL if one of the operands happens to be NULL.  Why I like to use SFMT, is that IMHO it makes code easier to read when the strings you are trying to join together contains commas …

LET select_clause = column1, ", ", column2
LET select_clause = SFMT("%1, %2", column1, column2)

or quotes

LET where_clause = "column1 = '",value1,"'"
LET where_clause = SFMT("column1 = '%1'", value1)

as you don’t have to stare closely to determine if the quote or comma is part of the resultant string, or part of the syntax joining the string together

With a USING clause, it makes it clear where the USING clause is being applied

DISPLAY "Value1 = ",value1 USING "##&", "AND Value2 =", value2 USING "##&"
DISPLAY SFMT("Value1 = %1 AND Value2 = %2", value1 USING "##&", value2 USING "##&" )

It also gives you more flexibility if the order of parameters can change.

CASE output_format
   WHEN "code" LET output_string = "%1" # e.g. FRA
   WHEN "desc" LET output_string = "%2" # e.g. France
   WHEN "code_desc LET output_string = "%1-%2" # e.g. FRA-France
   WHEN "desc_code" LET output_string = "%2 (%1)" # e.g. France (FRA)
CALL cb.addItem(code,  SFMT(output_string, code, desc))

So to answer question, append (,) or concatenate (||), pick the one that has the desired behaviour around NULLs, but also consider the SFMT operator. Next time you are staring at a long appended, or concatenated string expression, typically a string that is being used to create an SQL statement, and you are getting tied in knots trying to interpret the , and “, consider rewriting the code using SFMT operator, you may find it easier to read.