Strings to Rows

String to Rows

This section presents various techniques for transforming a string of delimited values like this one

C

----------

v1,v2,v3

into a number of rows with values appearing in a single column, like this.

  POSITION VAL

---------- ----------

         1 v1

         2 v2

         3 v3

We will use strings of comma separated values (CSV) in most of our examples, but the techniques can be easily modified for fixed format strings, pipe delimited strings, tab delimited strings, or quoted values by subtituting the appropriate parsing logic from SQL Techniques Tutorials: String to Columns for the CSV parsing logic in the examples.

Caveat

Storing more than one value in a delimted string in a single column (aka a "repeating group") often leads to performance and maintenance problems. Such designs break the first rule of Database Normalization, First Normal Form, giving you a non-normal table. Operations that are trivial with normalized tables become unecessarily complex with non-normal tables (see the solution to this requirement for an example of this complexity). If you are designing a new system avoid storing data in delimited strings whenever possible.

The techniques in this section are intended for cases where delimited strings are encountered outside the database or for those of you who must use them for reasons beyond your control.