In PostgreSQL, the CONCAT_WS()
function concatenates two or more strings, placing a separator between each one. The separator is specified by the first argument.
Syntax
The syntax goes like this:
concat_ws(sep text, str "any" [, str "any" [, ...] ])
Where sep text
is the separator to use.
Example
Here’s an example to demonstrate:
SELECT CONCAT_WS(',', 'Red', 'Green');
Result:
Red,Green
In this case, I concatenated the strings with a comma as the separator.
Here’s an example that uses a space as the separator:
SELECT CONCAT_WS(' ', 'Squid', 'Game');
Result:
Squid Game
Concatenating Numbers
PostgreSQL concatenates the text representation of its arguments, so we can concatenate numbers without having to explicitly convert them to a string:
SELECT CONCAT_WS(',', 1, 2, 3);
Result:
1,2,3
Although, be careful if there are leading zeros:
SELECT CONCAT_WS(',', 001, 002, 003);
Result:
1,2,3
Null Arguments
Null arguments are ignored:
SELECT CONCAT_WS(' ', 'Player', NULL, 456);
Result:
Player 456
No Arguments
Calling CONCAT_WS()
without passing any arguments results in an error:
SELECT CONCAT_WS();
Result:
ERROR: function concat_ws() does not exist LINE 1: SELECT CONCAT_WS(); ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts.
Passing an Array
It’s possible to pass an array to the CONCAT_WS()
function so that each array element becomes treated as a separate argument to be concatenated. To do this, prefix the array with the VARIADIC
keyword:
SELECT CONCAT_WS( '-', VARIADIC ARRAY[ 'Cat', 'Dog', 'Horse' ] );
Result:
Cat-Dog-Horse
We can see that each element in the array has become a separate string to be concatenated, and the separator works just as it did in the previous examples.