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.
Access this chapter
Tax calculation will be finalised at checkout
Purchases are for personal use only
Author information
Authors and Affiliations
Rights and permissions
Copyright information
© 2020 Kim Berg Hansen
About this chapter
Cite this chapter
Berg Hansen, K. (2020). Splitting Delimited Text. In: Practical Oracle SQL. Apress, Berkeley, CA. https://doi.org/10.1007/978-1-4842-5617-6_9
Download citation
DOI: https://doi.org/10.1007/978-1-4842-5617-6_9
Published:
Publisher Name: Apress, Berkeley, CA
Print ISBN: 978-1-4842-5616-9
Online ISBN: 978-1-4842-5617-6
eBook Packages: Professional and Applied ComputingProfessional and Applied Computing (R0)Apress Access Books