Skip to main content

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

See Also

FeedbackOpens in a new tab