CONCAT_WS

Description

The CONCAT_WS function is used to concatenate multiple strings or string elements in an array into a single string. The function can concatenate the input strings or string elements in an array based on the specified separator sep. If an input string is NULL, it is ignored in the result.

Syntax

CONCAT_WS(sep, str1, str2, ..., strN)
CONCAT_WS(sep, array1, array2, ..., arrayN)

Parameters

• sep: Separator string used to join the input strings.
• str1, str2, ..., strN: Strings to be joined.
• array1, array2, ..., arrayN: Arrays containing string elements to be joined.

Return Result

Returns a concatenated string.

Usage Example

1. Basic usage:

SELECT CONCAT_WS('-', 'hello', 'world');

Results:

hello-world

2. Connect multiple strings:

SELECT CONCAT_WS('-', 'hello', 'my', 'friend', '!');

Results:

hello-my-friend-!

3. Ignore NULL values:

SELECT CONCAT_WS('-', 'hello', NULL, 'world', NULL);

Results:

hello-world

4. Using Arrays to Concatenate Strings:

SELECT CONCAT_WS('-', array('hello', 'my', 'friend'), array('is', NULL, 'awesome'));

Results:

hello-my-friend-is-awesome

5. Use in combination with other functions:

SELECT CONCAT_WS('-', UPPER('hello'), LENGTH('world'), LOWER('!'));

Results:

HELLO-5-!

Notes

• When the input string or array elements are NULL, the CONCAT_WS function will ignore these values.
• If all input strings or array elements are NULL, an empty string is returned.
• If the separator sep is also NULL, a NULL is returned.

Through the above examples and explanations, you can better understand the usage and functionality of the CONCAT_WS function. In practical applications, you can flexibly use this function to concatenate strings or array elements as needed.