The concat_ws() function in PostgreSQL is used to concatenate multiple strings into one string with a specified separator. concat_ws stands for “Concatenate With Separator” and is useful for combining multiple columns or values into a single string with a separator between each value.
Syntax – concat_ws() function in PostgreSQL
concat_ws(separator text, string1 text [, string2 text, …])
- separator: The string to be used as a separator between the concatenated values.
- string1, string2, …: The strings to concatenate.
Basic Examples
1. Concatenate multiple Strings with a Hyphen Separator
SELECT concat_ws('-', 'Hello', 'PostgreSQL', 'World');
Explanation: Concatenates ‘Hello’, ‘PostgreSQL’, and ‘World’ with a Separator of Hyphen (-) between each string.
Output:
2. Concatenate multiple Strings with a Space Separator
SELECT concat_ws(' ', 'Hello', 'PostgreSQL', 'World');
Explanation: Concatenates ‘Hello’, ‘PostgreSQL’, and ‘World’ with a space between each string.
Output:
Using concat_ws() on Table Columns
You can use concat_ws() to combine values from different columns in a table, to demonstrate the examples lets use “employeetbl” Table
1. Concatenate multiple columns with a Space Separator
You can use concat_ws() to create a column or a formatted string column combining multiple columns with space separator :
SELECT *, concat_ws(' ', first_name, last_name) AS fullname
FROM employeetbl;
Explanation: Concatenates first_name and last_name columns with a space between both the columns and store it as full_name column.
Output:
2. Concatenate multiple columns with hyphen Separator
You can use concat_ws() to create columns string combining multiple columns with Hyphen (-) separators
SELECT *, concat_ws('-', first_name, last_name,eno::Text) AS employee_detail
FROM employeetbl;
Explanation: Concatenates first_name, last_name & eno columns with a hyphen between all the columns and store it as employee_detail column.
Output:
