Sybase SQL Anywhere - STRING Function - Concatenate Strings

STRING function concatenates one or more strings into a single string.

Quick Example:

   -- Concatenate strings AB and CD
   SELECT STRING('AB', 'CD');
   -- Result: ABCD

STRING Function Overview

Summary information:

Syntax STRING(string_expression, …)
Return Type LONG VARCHAR
NULL Values NULL value in a parameter is treated as '' (empty string)
But STRING returns NULL if all parameters are NULL
Parameter Conversion Number and datetime parameters are implicitly converted to string before concatenation
Single Parameter STRING with one parameter can be used to convert an expression to string
Alternatives || operator NULL is '' Implicit casting
+ operator Result is NULL if any parameter is NULL Explicit casting

Last Update: Sybase SQL Anywhere 12.0

STRING Function Details

STRING function concatenates one or more string expressions into a single string:

   SELECT STRING('A', 'B', 'C');
   -- Result: ABC

NULL Is Empty String

NULL value in any parameter is treated as '' (empty string):

   SELECT STRING('A', NULL, 'B');
   -- Result: AB

But if all parameters evaluate to NULL, STRING returns NULL, not empty string:

   SELECT STRING(NULL, NULL, NULL);
   -- Result: NULL

Non-String Parameters Implicit Conversion

STRING function implicitly converts numbers and datetime values to string before concatenation:

   SELECT STRING(1, 2, 3);
   -- Result: 123

|| and + Concatenation Operators

You can also use || and + operators to concatenate strings.

|| operator is equal to STRING function, it treats a NULL parameter as '' (empty string), and casts non-string expressions to string implicitly:

  SELECT 'A' || NULL || 'B';
  -- Result: AB
 
  SELECT 1 || 2 || 3;
  -- Result: 123

+ operator returns NULL if any expression is NULL, and it also requires explicit casting for non-string data types:

  SELECT 'A' + NULL + 'B';
  -- Result: NULL
 
  -- Arithmetic operation is performed without casting to string
  SELECT 1 + 2 + 3;
  -- Result: 6

STRING Function Conversion to Other Databases

String concatenation in other databases ( denotes differences):

Oracle:

CONCAT(string1, string2) 2 arguments only NULL is '' (empty string)
|| Operator NULL is ''

For more information, see Convert STRING to Oracle.

SQL Server:

+ Operator If any value is NULL, the result is NULL, unless CONCAT_NULL_YIELDS_NULL is OFF
+= Operator Transact-SQL only s1 += s2 is equal to s1 = s1 + s2
|| Operator Not available
CONCAT Function Not available

MySQL:

|| Operator Result is NULL if any value is NULL sql_mode PIPES_AS_CONCAT or ANSI
must be set
CONCAT(s1, s2, ...) Result is NULL if any value is NULL
CONCAT_WS(sep, s1, s2, …) NULL is '' Allows to specify a separator
GROUP_CONCAT NULL values skipped Concatenates values within a group

PostgreSQL:

|| Operator If any value is NULL, the result is NULL
CONCAT(s1, s2, …) NULL is '' Implicit Casting
CONCAT_WS(sep, s1, s2, …) NULL is '' Allows to specify a separator

Sybase SQL Anywhere Resources

Sybase SQL Anywhere 12.0 Manual