String to Columns

These tutorials show how to take strings like the following, which contain values delimited using various popular methods,

DATA_SET                  STRING
------------------------- ----------------------------------------
CSV                       a,bb,c c c

Double Quotes             "a", "b,b", "c c c"

Fixed Format              a    b,b  c c c

Pipe Delimited            a|b,b|c c c

Single Quotes             'a', 'b,b', 'c c c'

Single Quotes No Commas   'a' 'b,b' 'c c c'

Tab Delimited             a	b,b	c c c

XML - 1                   <V>a</V> <V>b,b</V> <V>c c c</V>

XML - 2                   <V1>a</V1> <V2>b,b</V2> <V3>c c c</V3>
 

and extract the values into separate columns like these.

DATA_SET                  VAL_1  VAL_2  VAL_3
------------------------- ------ ------ ------
CSV                       a      bb     c c c

Double Quotes             a      b,b    c c c

Fixed Format              a      b,b    c c c

Pipe Delimited            a      b,b    c c c

Single Quotes             a      b,b    c c c

Single Quotes No Commas   a      b,b    c c c

Tab Delimited             a      b,b    c c c

XML - 1                   a      b,b    c c c

XML - 2                   a      b,b    c c c
 

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.




Linking to SQL Snippets ™

To link to this page in Oracle Technology Network Forums or OraFAQ Forums cut and paste this code.

  • [url=http://www.sqlsnippets.com/en/topic-11989.html]SQL Snippets: SQL Techniques Tutorials - String to Columns[/url]

To link to this page in HTML documents or Blogger comments cut and paste this code.

  • <a href="http://www.sqlsnippets.com/en/topic-11989.html">SQL Snippets: SQL Techniques Tutorials - String to Columns</a>

To link to this page in other web sites use the following values.

  • Link Text : SQL Snippets: SQL Techniques Tutorials - String to Columns
  • URL (href): http://www.sqlsnippets.com/en/topic-11989.html