CONCAT
Given two arguments, the CONCAT function concatenates them into a single output string.
Note: You can also concatenate two arguments in-line using the concatenation operator (||); for example, A || B.
Syntax
CONCAT (argument1, argument2)
Remarks
• The arguments of CONCAT can be of type string or any other type, and you can concatenate them in any combination of data types.
• To concatenate a nonstring to a string, use the CAST function to convert the nonstring to string.
• Enclose a literal string within single-quotes to concatenate it with another argument. For example, CONCAT(‘string1’, string2), where string1 is a literal.
• The CONCAT function does not supply white-space characters between arguments in the concatenated output. You must provide the white-space characters manually.
You can use the Subfunction button in the Function Arguments Input dialog to provide a space between concatenated strings, or use the format:
CONCAT('string1', CONCAT(' ', 'string2'))
• If any of the input strings in a CONCAT function is NULL, the result string is also NULL. Otherwise, the output type is STRING.
The following table lists the input types that you can use in CONCAT.
Data Type of argument1 | Data Type of argument2 | Output Type |
BIGINT, CHAR, DATE, DECIMAL, FLOAT, INTEGER, LONGVARCHAR, NUMERIC, REAL, SMALLINT, STRING, TIME, TIMESTAMP, TINYINT, VARCHAR | Any type listed for argument1 except NULL.n | STRING |
Any data type listed above. | NULL | NULL |
NULL | | NULL |
Examples (Generic)
CONCAT (<string>, <string>)
CONCAT (<string>, <nonstring>)
CONCAT (<nonstring>, <string>)
CONCAT (<nonstring>, <nonstring>)
Examples (Specific)
SELECT CONCAT (customers.ContactFirstName,
CONCAT (' ', customers.ContactLastName)) Expr1,
CONCAT ('a', concat(' ', 'b')) Expr2,
CONCAT ('a', concat(' ', NULL)) Expr3,
CONCAT ('NULL', concat(' ', NULL)) Expr4,
CONCAT (NULL, concat(' ', NULL)) Expr5,
CONCAT ('a', current_date) Expr6,
CONCAT (current_date, current_time) Expr7,
CONCAT ('Feb', concat(' ', CAST(2004 AS BIT))) Expr8,
customers.ContactFirstName || ' ' ||
customers.ContactLastName Expr9,
'0100' || '1010' Expr10, 100 || 1010 Expr11, 23 || 56 Expr12
FROM /shared/examples/ds_orders/customers customers