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.