WM_CONCAT

Overview

The WM_CONCAT function is used to concatenate a column of values with a specified delimiter. This function can handle string type data and can choose to either remove duplicates or retain all values during concatenation.

Syntax

wm_concat([distinct] separator, col)

Parameter Description

• separator: A string constant used as a delimiter for concatenated values.
• col: A string representing the column to be concatenated.

Return Result

Returns a string value containing the concatenated result. If the distinct keyword is set, the calculation will be performed on the deduplicated set; otherwise, all values will be retained. null values are not included in the calculation.

Example

1. Concatenation using &&

SELECT wm_concat('&&', col) FROM VALUES ('row1'), (null), ('row3') AS t(col); +----------------------+ | wm_concat('&&', col) | +----------------------+ | row1&&row3 | +----------------------+

2. Connections with Separators:

SELECT wm_concat(',', col) FROM VALUES (1), (null), (3) AS t(col); +---------------------+ | wm_concat(',', col) | +---------------------+ | 1,3 | +---------------------+

3. Deduplicate Connections and Group:

SELECT k, wm_concat(DISTINCT '|', v) FROM VALUES (1, 'ALLEN'), (1, null), (1, 'ALLEN'), (2, 'KING'), (2, 'ALEX') AS t(k, v) GROUP BY k; +---+----------------------------+ | k | wm_concat(DISTINCT '|', v) | +---+----------------------------+ | 1 | ALLEN | | 2 | KING|ALEX | +---+----------------------------+

4. Handling spaces and special characters when connecting:

SELECT wm_concat(' - ', col) FROM VALUES ('John Doe'), ('Jane Smith'), (null), ('Alice Jones') AS t(col); +-------------------------------------+ | wm_concat(' - ', col) | +-------------------------------------+ | John Doe - Jane Smith - Alice Jones | +-------------------------------------+

Notes

• When the values in the col column are null, the WM_CONCAT function will not include them in the result.
• If the columns to be concatenated contain spaces or special characters, make sure to use appropriate delimiters to avoid ambiguity.
• Using the distinct keyword can effectively remove duplicate string values, but be aware that this may affect performance. Use this option with caution when handling large amounts of data.