CONCAT (SQL)

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

Synopsis

{fn CONCAT(string1,string2)}

Description

{fn CONCAT(string1,string2)} concatenates two strings and returns a concatenated string. This syntax is equivalent to using the concatenate operator (||). You can also use the STRING function to concatenate two or more expressions into a single string.

This statement selects the top 5 first names and last names from a table, concatenating the LastName and FirstName columns and separating them by a comma.

SELECT TOP 5
FirstName, LastName,
{fn CONCAT({fn CONCAT(LastName, ',')}, FirstName)} AS FullName
FROM Sample.Person

FirstName	LastName	FullName
Quigley	Ulman	Ulman,Quigley
Buzz	Woo	Woo,Buzz
Mario	Mastrolito	Mastrolito,Mario
Julie	Noodleman	Noodleman,Julie
Lawrence	Quincy	Quincy,Lawrence

Example: Concatenate Two Strings

Arguments

string1,string2

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).

You can concatenate any combination of numerics or numeric strings; the concatenation result is a numeric string. InterSystems 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

Examples

Concatenate Two Strings

This statement 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 TOP 5
  {fn CONCAT({fn CONCAT(HomeCity,', ')}, HomeState)} AS LocationWithConcatFunction,
  HomeCity||', '||HomeState AS LocationWithConcatOperator
FROM Sample.Person

LocationWithConcatFunction	LocationWithConcatOperator
Denver, CO	Denver, CO
Boston, MA	Boston, MA
Albuquerque, NM	Albuquerque, NM
Jacksonville, FL	Jacksonville, FL
Lexington, KY	Lexington, KY

This statement concatenates a string and a NULL, which returns a column of NULLs.

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

StrNull
NULL
NULL
NULL
NULL
NULL

This statement shows that numbers are converted to canonical form before concatenation. To avoid this, you can specify the number as a string, as shown in the second part of this statement.

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

StrNum	StrStrNum
CO1200	CO0012.00E2
MA1200	MA0012.00E2
NM1200	NM0012.00E2
FL1200	FL0012.00E2
KY1200	KY0012.00E2

The statement shows that trailing blank spaces are retained. When you concatenate a two-letter state field with 10 spaces, the length of each value in the concatenated column is 12.

SELECT TOP 5
HomeState,
CHAR_LENGTH({fn CONCAT(HomeState,'          ')}) AS StrSpace
FROM Sample.Person2

HomeState	StrSpace
CO	12
MA	12
NM	12
FL	12
KY	12