In my previous post SQL Tips & Techniques- String Aggregation, I discussed how to perform string aggregation. Recently, I received a question to do the reverse using SQL i.e. String Splitting.
Listing 1
Listing 1
For example, we have a table myTab2 with column values as shown above. As you can see, the string value in LIST column is a list of sub-strings separated with “, “. Now, we would like to split the string in LIST column and present the sub-strings in separate rows. Hence, the query should return two rows for CATEGORY = 1, three rows for CATEGORY = 2 and two rows for CATEGORY = 3.
Here is the query that performs the required string splitting.
Listing 2
The above query uses recursive SQL and it will return the following.
Listing 3
I learned the above use of recursive SQL from Knut Stolze’s developerWorks article Parsing Strings in SQL. If you know a different way to do the string splitting using SQL, please share it.
