How to Split Strings in SQL with Multiple Delimiters Using regexp_substr

Learn how to effectively split strings in SQL tables using `regexp_substr`, even when string_split is not supported. This guide provides a modified SQL query to generate multiple rows while preserving all data.
---

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Split string using delimiters and put them in multiple rows in sql not working

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Handling String Splitting in SQL: A Comprehensive Guide

When dealing with databases, you often encounter situations where you need to split string values into multiple rows. This is particularly challenging when your SQL configuration does not support string_split. If you find yourself in a similar predicament, fear not! This guide will guide you through the process of splitting strings using regular expressions in SQL.

The Problem

Imagine you have a messages table consisting of various columns that may contain either colon-separated or comma-separated values. Your goal is to transform these multiple values into separate rows.

Example of the messages Table

usernameid_criteriacode_criteriaKim2221-23:21-22Tim36AllSam21,22,23:24:25:(null)In this example, we aim to derive each component of the id_criteria and code_criteria into its individual row.

The Initial Attempt

As a starting point, you might try using a SQL query that employs the regexp_substr function to split the string based on your delimiters. Below is the original SQL query that you might have used:

[[See Video to Reveal this Text or Code Snippet]]

While this may yield some results, you might find that not all rows are displayed as expected. For instance, you might receive output like this:

usernameid_criteriacode_criteriaSam23(null)Sam24(null)Sam25(null)Expected Output

What you truly desire is the following output when correctly splitting the criteria:

usernameid_criteriacode_criteriaKim2221-23:21-22Tim36AllSam21(null)Sam22(null)Sam23(null)Sam24(null)Sam25(null)The Solution: Modifying the Query

To achieve the desired output, we need to adjust the SQL query. We’ll use multiple CONNECT BY clauses to generate rows for values in both id_criteria and code_criteria. Here’s the modified SQL query for splitting both criteria:

[[See Video to Reveal this Text or Code Snippet]]

Explanation of the Modified Query

regexp_substr(...): This function is being used to isolate individual values from the id_criteria and code_criteria columns based on the specified delimiters.

CONNECT BY: This clause creates a hierarchy which allows us to generate multiple rows based on the parent-child relationship defined by the previous row data.

PRIOR keyword: This keyword helps maintain the relationship between rows and ensures that data grouping is preserved correctly.

Example Output

When you run the modified query, the expected output reflects the separated values accurately:

USERNAMEIDREFVALCODEREFVALKim2221-23:21-22Tim36AllSam21(null)Sam22(null)Sam2324Sam25(null)Conclusion

With the help of the regexp_substr function and appropriate query structure, you can effectively split strings in SQL tables to display the data in an organized manner. This solution is particularly useful when built-in functions like string_split are not available in your SQL environment.

By following this guide, you should now be able to confidently manipulate string data within your SQL applications. Happy coding!