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