PostgreSQL Array to String Function in Windows 10:
The “array_to_string” function in PostgreSQL in Windows 10 simply takes two integers, i.e., the array to be converted and the delimiter with which the array elements will be separated once they will be concatenated to form a string. This function can also accept a third parameter that can handle the NULL values within an array. You will be able to understand the usage of this function of PostgreSQL in Windows 10 more clearly once you go through the following examples.
Example # 1: Concatenating the Elements of an Integer Array:
If you have an integer array, i.e., an array of numbers, and you want to concatenate all of its elements while separating them with the help of a specific delimiter, then you will have to execute the following query:
Now, let us try to understand this query. The “SELECT” statement used at the beginning of this query will simply display the output on the console. Then, we have the “array_to_string” function. This function takes an array and the delimiter with which the string will be separated as an input. Moreover, there is a third (optional) argument for this function, but we will discuss it in the proceeding examples. Then, we have the “ARRAY” keyword that is used to declare an array of any desired data type. Here, we have declared an integer array that contains numbers from 1 to 10. Then, we have specified the delimiter, i.e., “:” in this case. It means that when our specified array is converted to a string, its elements will be separated by colons.
When we executed this query, we obtained the desired results as shown in the image below:
Example # 2: Concatenating the Elements of an Integer Array (that also contains Some NULL Values) Separated by a Specified Delimiter:
We will tweak the first example a little by using the same array with some NULL values. We basically want to explore how this will affect the results of our query. The query with this modification is as follows:
You can see that there are some NULL values too in our array this time. After introducing these values, we got the results shown in the image below. You can see that in the output of this query, the NULL values of our array were ignored.
Example # 3: Concatenating the Elements of an Integer Array (that also contains Some NULL Values) Separated by a Specified Delimiter and Replacing the NULL Values with a Specified Character:
We will see how we can gracefully replace the NULL values within an array with a specific character. In this way, whenever an array with some NULL values will be encountered, those NULL values will be replaced by that specified character. This character is, in fact, the third (optional) argument of the “array_to_string” function. The query for this example is as follows:
The output of this query shows that the NULL values are replaced by the “&” symbol.
Example # 4: Concatenating the Elements of a Character Array:
In this example, we will simply try to concatenate the elements of a character array by executing the following query:
The array in this query has three different values, which are, in fact, three different names. All we want to do is to concatenate these names while separating them with the “@” delimiter.
The concatenated elements of this array separated by the “@” symbol in the form of a string are shown in the image below:
Example # 5: Creating a Complete Name Out of a Character Array:
We will try to play around with character arrays in this example and the proceeding examples by using the “array_to_string” function in PostgreSQL in Windows 10. If there are different characters present in an array and these characters together can form a complete name, then we can even join them using this function. However, we cannot use any character delimiter in such a query other than an empty space. Only then will those characters be joined to form a complete name. You can check out the following query to get better clarity:
You can see from the output displayed in the image below that the characters of the specified array have been converted into a complete string of names where there are no delimiters in between since the delimiter that we specified in our query was NULL or an empty space.
Example # 6: Creating a Full Name through a Character Array:
We can also create a full name, i.e., a combination of a person’s first name and last name, by using the “array_to_string” function in PostgreSQL in Windows 10. For that, we will simply use a special character within an array as one of its indexes for separating the first name from the last name. Again, the delimiter for this query will be NULL or an empty space. This query is as follows:
When we executed this query, the character array got converted into a string of a full name in which the first name and last name was separated by the “_” symbol as shown below:
However, alternatively, we can also have only two elements in this array, i.e., ‘aqsa’ and ‘yasin’, and we can set the delimiter for this query as ‘_’. The results of this modified query will exactly be the same as shown above.
Example # 7: Extracting an Email ID from a Character Array:
Finally, we can also extract an email ID from a character array by using the “array_to_string” function while setting the delimiter as NULL or an empty space. You can run the following query to achieve this result:
There are a total of five elements in this array, and they all will be concatenated to form a complete email ID as shown in the image below:
Conclusion:
After going through this article, you will learn the usage of the “array_to_string” function of PostgreSQL very well. However, you must log in to your PostgreSQL server before proceeding with these examples.
