String to array in PostgreSQL – string_to_array()

The string_to_array() function in PostgreSQL is used to split a string into an array based on a specified delimiter. This function is useful for breaking down a string into its components and storing them as elements of an array.

Syntax – string_to_array() function in PostgreSQL

• string: The input string to be split into an array.
• delimiter: The delimiter used to split the string.

Split String to Array by Comma

 

SELECT string_to_array('Mercury,Venus,Earth,Mars', ',');

Explanation: Splits the string ‘Mercury,Venus,Earth,Mars’ into an array with ‘Mercury’, ‘Venus’, ‘Earth’ and ‘Mars’ as elements.

Output:

Split String to array by Space

 

SELECT string_to_array('Hello PostgreSQL Enthusiast', ' ');

Explanation: Splits the string ‘Hello PostgreSQL Enthusiast’ into an array with each word separated by commas

Output:

Split String to array by Dash / hyphen

 

SELECT string_to_array('20-AUG-2024', '-');

Explanation: Splits the string ’20-AUG-2024′ into an array with each element separated by Dash or hyphen

Output:

Split String to array on a Column in PostgreSQL:

You can use string_to_array() function to split the values of a column in a table into arrays.

Let’s use the table name “orders_items”

You can use string_to_array() function to split the basket column into arrays as shown in the below code

 

SELECT *, string_to_array(basket, ',') AS basket_array
FROM orders_items;

• basket column is split into arrays by and stored as a new column named “basket_array”.

Output:

Accessing Array Elements in PostgreSQL:

Once you have an array, you can access its elements using array indexing. PostgreSQL arrays are 1-based, meaning the first element is at index 1.

Example: To Access First Element of the Array, First convert the “basket” column from string to array using  string_to_array() function and then get the first element by indexing first element

 

SELECT *, (string_to_array(basket, ','))[1] AS first_item
FROM orders_items;

Output:

Example: Access Last Element of the Array,  First convert the “basket” column from string to array using  string_to_array() function and then get the last element by using array_length() function as shown below

 

SELECT *, (string_to_array(basket, ',')) [array_length(string_to_array(basket, ','),1)] AS last_item 
FROM orders_items;

Output:

Example: Count Number of Items

You can use the cardinality() function to count the number of items in the array. First convert the “basket” column from string to array using  string_to_array() function and then count the number of elements in array using cardinality() function as shown below

 

SELECT *, cardinality(string_to_array(basket, ',')) AS item_count
FROM orders_items;

Output: