Splitting Delimited Text

Abstract

Particularly if you get data from somewhere else, it is not uncommon to get it in the form of a string with a list of values separated by some delimiter, typically comma, semicolon, tab, or similar. As you most often don’t know the number of elements in the list, you can’t just use substr to split it into a fixed number of columns. Instead it is normally most useful to be able to turn the list into rows, so you can treat it as a table in your SQL. Such splitting involves generating rows, which you can do in many ways. Some different methods are shown, ranging from using PL/SQL to loop over the elements of the list and generating a row at a time, over generating all rows at once by selecting from dual and retrieving the elements for each row from the list, to pretending the list is JSON and parsing it with native JSON functionality.