More Examples to Concatenate Strings

Let’s understand through the technical example. Let’s create the table and insert some data inside it.

Query:

CREATE TABLE language (
  state VARCHAR(50),
  name VARCHAR(50)
);

INSERT INTO language VALUES ('Bihar', 'Hindi');
INSERT INTO language VALUES ('Punjab', 'Punjabi');
INSERT INTO language VALUES ('Assam', 'Assamese');
INSERT INTO language VALUES ('Punjab', 'Urdu');
INSERT INTO language VALUES ('Bihar', 'English');
INSERT INTO language VALUES ('Bihar', 'Maithili');
INSERT INTO language VALUES ('Punjab', 'Hindi');
INSERT INTO language VALUES ('Assam', 'Bengali');
INSERT INTO language VALUES ('Maharashtra', 'Marathi');
INSERT INTO language VALUES ('Gujarat', 'Gujarati');
INSERT INTO language VALUES ('Maharashtra', 'Hindi');
INSERT INTO language VALUES ('Maharashtra', 'English');
INSERT INTO language VALUES ('Assam', 'Bodo');

Output:

Explanation: As we can see in the image, the above table contains the various languages spoken in various states of India.

Let’s use STRING_AGG() function to concat the different languages spoken in a particular state. First we will start by the vanilla version of the query to use STRING_AGG. We will group using the state column and concat the values in the name column.

Query:

SELECT state, STRING_AGG(name, ',') as different_languages
FROM language
GROUP BY state;

Output:

Explanation: This query groups rows from the language table by the state column and concatenates values from the name column for each group, separated by commas. The result set includes unique values from state and their concatenated name values, representing different languages spoken in each state.

Now we will make use of ORDER BY clause to order the different languages in ascending order.

Query:

SELECT state, STRING_AGG(name, ','  ORDER BY name) as different_languages
FROM language
GROUP BY state;

Output:

Explanation: This query is similar to the previous one, but it adds an ORDER BY clause within the STRING_AGG function. It concatenates values from the name column for each group, orders them alphabetically by name, and separates them by commas. The result set includes unique values from state and their concatenated

We can even use the second argument of the function to define a custom separator to concat the different languages.

Query:

SELECT state, STRING_AGG(name, ';') as different_languages
FROM language
GROUP BY state;

Output:

Explanation: This query groups rows from the language table by the state column and concatenates values from the name column for each group, separated by semicolons (;). The result set includes unique values from state and their concatenated name values, representing different languages spoken in each state, separated by semicolons.

In database management, aggregating and concatenating strings is a common requirement. PostgreSQL provides a powerful solution for this with the STRING_AGG function. This article explores how to leverage STRING_AGG to concatenate strings in PostgreSQL efficiently, offering multiple approaches to cater to various situations. In this article, we will understand how to use STRING_AGG effectively with the help of various examples and so on.