CONCAT

A scalar string function that returns a character string as a result of concatenating two character expressions.

Synopsis

{fn CONCAT(string-expression1,string-expression2)}

Arguments

string-expression1, string-expression2

The string expressions to be concatenated. The expressions can be the name of a column, a string literal, a numeric, or the result of another scalar function, where the underlying data type can be represented as any character type (such as CHAR or VARCHAR).

Description

CONCAT concatenates two strings to return a concatenated string. You can perform exactly the same operation using the concatenate operator (||).

You can concatenate any combination of numerics or numeric strings; the concatenation result is a numeric string. Caché SQL converts numerics to canonical form (exponents are expanded and leading and trailing zeros are removed) before concatenation. Numeric strings are not converted to canonical form before concatenation.

You can concatenate leading or trailing blanks to a string. Concatenating a NULL value to a string results in a NULL; this is the industry-wide SQL standard.

The STRING function can also be used to concatenate two or more expressions into a single string.

Examples

The following example concatenates the Home_State and Home_City columns to create a location value. The concatenation is shown twice, using the CONCAT function and the concatenate operator:

SELECT {fn CONCAT(Home_State,Home_City)} AS LocationFunc,
Home_State||Home_City AS LocationOp
FROM Sample.Person

The following example shows what happens when you attempt to concatenate a string and a NULL:

SELECT {fn CONCAT(Home_State,NULL)} AS StrNull
FROM Sample.Person

The following example shows that numbers are converted to canonical form before concatenation. To avoid this, you can specify the number as a string, as shown:

SELECT {fn CONCAT(Home_State,0012.00E2)} AS StrNum,
{fn CONCAT(Home_State,'0012.00E2')} AS StrStrNum
FROM Sample.Person

The following example shows that trailing blank spaces are retained:

SELECT CHAR_LENGTH({fn CONCAT(Home_State,'          ')}) AS StrSpace
FROM Sample.Person