CONCAT_WS

MySQL

CONCAT_WS(separator, string1, string2, …, stringN)

• separator: This is the string that will be used to join the other string arguments. If the separator is NULL, the result is NULL.
• string1, string2, …, stringn: These are the strings that will be concatenated together. They are separated by the first separator argument. If any string value is NULL, then the CONCAT_WS() function treats it as an empty string (”).

Example

SELECT CONCAT_WS(',', 'MySQL', 'MongoDB', 'PostgreSQL');

Output

MySQL,MongoDB,PostgreSQL

Explanation

The CONCAT_WS function in MySQL is used for concatenating multiple strings with a specified separator. In the given example, the strings ‘MySQL’, ‘MongoDB’, and ‘PostgreSQL’ are combined into a single string separated by commas. The result is “MySQL,MongoDB,PostgreSQL”.

PostgreSQL

CONCAT_WS(separator text, str “any” [, str “any” …]) returns text

• separator text: This is the text that PostgreSQL will insert between each of the strings that it is combining.
• str “any” [, str “any” …]: These are the strings that PostgreSQL is set to combine. This function accepts any number of string inputs.
• returns text: This describes the type of output that the CONCAT_WS function produces. After combining the input strings with the separator, the function outputs a single text string.

Example

SELECT CONCAT_WS(',', 'PostgreSQL', 'SQL', 'Database');

Output

'PostgreSQL,SQL,Database'

Explanation

In the provided example, the CONCAT_WS function concatenates the strings ‘PostgreSQL’, ‘SQL’, and ‘Database’, with a comma serving as the separator. The output yields a single string: ‘PostgreSQL,SQL,Database’.

SQL Server

CONCAT_WS( separator, argument1, argument2 [, argumentN]… )

• separator: This is a string value that SQL Server uses to separate concatenated strings. It can be a single character or multiple characters.
• argument1: This is the first string value that you want to concatenate. The value can be a string literal, a variable, a column, or an expression that returns a string.
• argument2: This is the second string value to concatenate. Like argument1, it can be a string literal, a variable, a column, or an expression that returns a string.
• argumentn: Optional. The CONCAT_WS() function can take an unlimited number of arguments. argumentN represents the third, fourth, fifth, etc., string values to concatenate. Each of these arguments can be a string literal, a variable, a column, or an expression that returns a string.

Example

SELECT CONCAT_WS(', ','John','Doe','123 Main St') AS FullDetails;

Output

FullDetails
---------------------
John, Doe, 123 Main St

Explanation

The CONCAT_WS function in SQL Server concatenates multiple strings into one string with a specified separator. In the given example, the separator is a comma followed by a space (’, ’), and the strings to be joined are ‘John’, ‘Doe’, and ‘123 Main St’. The resultant string, ‘John, Doe, 123 Main St’, is returned as a single entity under the column alias ‘FullDetails’.