CONCAT function

Concatenates one or more values into a single string. Non-string values are implicitly cast to strings.

Syntax

CONCAT(value1 [, value2, ...])

Arguments

value1, value2, etc.: Values to concatenate. Non-string values (integers, booleans, dates, timestamps, decimals, intervals) are automatically converted to strings.

Returns

A VARCHAR containing the concatenated result of all input values.

NULL Handling

If any argument is NULL, the result is NULL.
This is consistent with the || operator behavior.

Implicit Type Conversion

CONCAT automatically converts non-string types to strings: - BIGINT: Converted to numeric string - BOOLEAN: Converted to 't' (true) or 'f' (false) - DECIMAL: Converted to numeric string with decimal places - DATE: Converted to 'YYYY-MM-DD' format - TIMESTAMP: Converted to 'YYYY-MM-DD HH:MI:SS.MS' format - INTERVAL: Format differs between Vertica and Databricks

Examples

-- Example 1: Concatenate strings
> SELECT CONCAT('a', 'b', 'c');
'abc'

-- Example 2: NULL returns NULL
> SELECT CONCAT('a', NULL);
NULL

> SELECT CONCAT(NULL);
NULL

-- Example 3: Implicit integer conversion
> SELECT CONCAT(239);
'239'

-- Example 4: Implicit boolean conversion
> SELECT CONCAT(TRUE);
't'

-- Example 5: Implicit decimal conversion
> SELECT CONCAT(DECIMAL '9999999.87654');
'9999999.87654'

-- Example 6: Implicit date conversion
> SELECT CONCAT(DATE '2020-10-10');
'2020-10-10'

-- Example 7: Implicit timestamp conversion
> SELECT CONCAT(TIMESTAMP '2000-01-02 03:04:05.678');
'2000-01-02 03:04:05.678'

-- Example 8: Mixed types
> SELECT CONCAT(0.1, 'A', -5, DATE '0001-01-01', true, TIMESTAMP '1999-12-31 23:59:59.999');
'0.1A-50001-01-01t1999-12-31 23:59:59.999'

-- Example 9: Integer and string
> SELECT CONCAT(10, '1');
'101'

Engine Differences

INTERVAL to String

Interval conversion differs between engines:

> SELECT CONCAT(INTERVAL '100-00' YEAR TO MONTH);
-- Vertica: '100-0'
-- Databricks: 'INTERVAL '100-0' YEAR TO MONTH'