PostgreSQL provides various array functions such as ARRAY_APPEND(), ARRAY_TO_STRING(), ARRAY_REPLACE(), etc. STRING_TO_ARRAY() is one of them. Each array function serves a unique functionality. For instance, the STRING_TO_ARRAY() function converts a string into an array.
This write-up will teach you how to use the STRING_TO_ARRAY() function in Postgres via suitable examples. So, let’s start!
How to Use the STRING_TO_ARRAY() Function in Postgres?
In PostgreSQL, STRING_TO_ARRAY() is a built-in array function that accepts three arguments: a string, a delimiter, and a text to replace the null values. The STRING_TO_ARRAY() function accepts a string as the first argument, splits it into array elements, and concatenates the array elements using a delimiter/separator. The separator can be any value, such as white space, comma, semi-colon, etc.
STRING_TO_ARRAY(str, sep[, text]);
In the above syntax:
- “str” represents a string to be converted into an array.
- “sep” represents a splitter based on which the given string will be split to array elements.
- “text” is an optional parameter that replaces the NULL values with the specified text.
Note: The return type of the STRING_TO_ARRAY() function will be an ARRAY.
Example 1: What Does the STRING_TO_ARRAY() Function Do in Postgres?
This example will explain the working of the STRING_TO_ARRAY() function in Postgres:
SELECT STRING_TO_ARRAY( 'HELLO WELCOME TO COMMAND PROMPT', ' ');
In the above example:
- A string is passed to the STRING_TO_ARRAY() function as the first argument.
- White space is passed as a second argument to the STRING_TO_ARRAY() function, which will split the given string from the white spaces:
The above snippet shows that the given string has been split into array elements.
Example 2: How Does the STRING_TO_ARRAY() Function Work on Table’s Data in Postgres?
We have created a table named “employee_data” that consists of three columns: emp_id, emp_name, and emp_email_address:
SELECT * FROM employee_data;
The above snippet shows that the emp_name column has string-type data. Let’s use the STRING_TO_DATE() function on the “emp_name” column to convert the string into an array:
SELECT STRING_TO_ARRAY(emp_name, ' ') FROM employee_data;
The above snippet authenticates that the STRING_TO_ARRAY() function was executed successfully. The STRING_TO_ARRAY() function splits the input string into array elements. The output shows that the return type of the returned array is text.
That’s it from this Postgres guide!
Conclusion
In PostgreSQL, STRING_TO_ARRAY() is a built-in array function that accepts three arguments: a string, a delimiter, and a text to replace the null values. The STRING_TO_ARRAY() function accepts a string as the first argument, splits it into array elements, and concatenates the array elements using a delimiter/separator. The separator can be any value, such as white space, comma, semi-colon, etc. This write-up explained Postgres' STRING_TO_ARRAY() function with practical examples.
