## Wednesday, November 24, 2010

Here's a slightly simpler and somewhat less powerful alternative to Monday's OPENSTRING and CROSS APPLY solution to the following problem:

"Let's say you have a table that contains a tab-delimited list of field values in a single string column, and you want to split those values apart and store them in separate columns in another table."
To make it simpler yet, let's change "tab-delimited list" to "comma-delimited" which is the default.

Here's the input table:
`CREATE TABLE source_table (   source_pkey   INTEGER NOT NULL PRIMARY KEY,   row           LONG VARCHAR NOT NULL );INSERT source_table VALUES ( 1, '21, ''Hello'',  99, 123.45\x0d\x0a' );INSERT source_table VALUES ( 2, '22, ''World'',  88, 456.78\x0d\x0a' );INSERT source_table VALUES ( 3, '23, ''Goodbye'',77, 234.56\x0d\x0a' );COMMIT;`

Each value stored in source_table.row is a string which in turn contains four field values; e.g., an integer 21, a string 'Hello', another integer 99, a decimal value 123.45 and so on. Commas separate the field values inside source_table.row, and a \x0d\x0a carriage return - line feed pair is used as row delimiter.

Here's the output table, with one column corresponding to each of the four field values stored in source_table.row:
`CREATE TABLE target_table (   target_pkey    INTEGER NOT NULL PRIMARY KEY,   data1          VARCHAR ( 10 ) NOT NULL,   data2          INTEGER NOT NULL,   data3          DECIMAL ( 11, 2 ) NOT NULL );`

Here's a LOAD TABLE statement that splits-and-copies the data from source_table.row into target_table:
`LOAD TABLE target_table   USING COLUMN row            FROM source_table           ORDER BY source_pkey;`

The USING clause on lines 2 through 4 tells the LOAD TABLE statement that
• the data is coming from COLUMN row,

• that column is coming FROM source_table, and

• and the rows are to be loaded in ORDER BY source_pkey.
Here's what the input and output look like:

Tip: If you only see one row in target_table, you probably forgot the row delimiter (\x0d\x0a in this case).