Concatenation Operator

The concatenation operator is a binary operator, whose syntax is shown in the general diagram for an SQL Expression. You can use the concatenation operator ( || ) to concatenate two expressions that evaluate to character data types or to numeric data types. These examples show some possible concatenated expression combinations.
  • The first example concatenates the zipcode column to the first three letters of the lname column.
  • The second example concatenates the suffix .dbg to the contents of a host variable called file_variable.
  • The third example concatenates the value that the TODAY operator returns to the string Date. 
    lname[1,3] || zipcode 

:file_variable || '.dbg'

'Date:' || TODAY
You cannot use the concatenation operator in the following embedded-language statements:
  • ALLOCATE COLLECTION
  • ALLOCATE DESCRIPTOR
  • ALLOCATE ROW
  • CREATE FUNCTION FROM
  • CREATE PROCEDURE FROM
  • CREATE ROUTINE FROM
  • DEALLOCATE COLLECTION
  • DEALLOCATE DESCRIPTOR
  • DEALLOCATE ROW DESCRIBE
  • DESCRIBE INPUT
  • EXECUTE
  • FLUSH
  • GET DESCRIPTOR
  • GET DIAGNOSTICS
  • PUT
  • SET AUTOFREE
  • SET CONNECTION
  • SET DESCRIPTOR
  • WHENEVER
Except as noted for the DECLARE and PREPARE statement, routines written in external languages, such as the language, cannot use the concatenation operator in the following dynamic SQL statements:
  • CLOSE
  • DECLARE
  • EXECUTE IMMEDIATE
  • FETCH
  • FREE
  • OPEN
  • PREPARE

Although input parameters of the DECLARE statement, such as a cursor_id specification, cannot be expressions that include the concatenation operator, routines can use this operator in a SELECT, INSERT, EXECUTE FUNCTION, or EXECUTE PROCEDURE statement within the DECLARE statement.

routines can use the concatenation operator in the text of the SQL statement or statements that you pass to the PREPARE statement.

In SPL routines, you can include the concatenation operator in an expression that specifies the text of the SQL statement that you pass to the EXECUTE IMMEDIATE statement or to the PREPARE statement, even if the calling context of the SPL routine is routines.

You cannot use the concatenation operator directly with user-defined data types, with complex or large-object data types, nor with operands that are not built-in character or number data types. You must explicitly cast UDTs or other unsupported data types to a built-in character or numeric data type before you can pass the result to the concatenation operator.

The data type of the result of a concatenation operation depends of the data types of the operands and on the length of the resulting string, using the return type promotion rules that the section Return Types from the CONCAT Function describes.

The concatenation operator ( || ) has an associated operator function called CONCAT. The CONCAT function cannot be overloaded.

When you define a text-based UDT, you can define a CONCAT function to concatenate objects of that user-defined data type. For more information, see HCL OneDB™ User-Defined Routines and Data Types Developer's Guide.