Article:
Working with delimited strings in PostgreSQL? Whether you're parsing a comma-separated list or breaking apart values based on another character, PostgreSQL offers powerful functions to split strings. Let's explore the most commonly used methods: split_part, regexp_split_to_table, and regexp_split_to_array.
✂️ 1. split_part() – Get a Specific Part of a String
Use split_part when you want to extract a specific piece from a delimited string.
✅ Syntax:
sql
split_part(string text, delimiter text, field int)
🔍 Example:
sql
SELECT split_part('apple,banana,cherry', ',', 2) AS result;
➡️ Output: banana
The function splits the string by the comma (,) and returns the second item.
📄 2. regexp_split_to_table() – Convert a String to Rows
Use regexp_split_to_table when you need each split value as a separate row.
✅ Syntax:
sql
regexp_split_to_table(string text, pattern text)
🔍 Example:
sql
SELECT regexp_split_to_table('apple,banana,cherry', ',') AS result;
➡️ Output:
markdown
result
--------
apple
banana
cherry
This is useful when working with sets or joins.
🧺 3. regexp_split_to_array() – Convert a String to an Array
Use this to get all split values as elements of an array.
✅ Syntax:
sql
regexp_split_to_array(string text, pattern text)
🔍 Example:
sql
SELECT regexp_split_to_array('apple,banana,cherry', ',') AS result;
➡️ Output: {apple,banana,cherry}
Great for looping or when array processing is required.
🧠 Which One Should You Use?
Use CaseRecommended FunctionGet a specific part of the stringsplit_part()Turn string into multiple rowsregexp_split_to_table()Get all parts as an arrayregexp_split_to_array()
Conclusion:
PostgreSQL provides flexible tools for string manipulation. Whether you need to extract a single field, generate multiple rows, or work with arrays, there's a function tailored to your needs.