split field into columns

Database
Teradata Employee

split field into columns

Hi all

I have a record with several fields, and the las one has a large text. 

This text has a separator ; so what i would like to do is go from 1 large field with separators to various columns.

This applies to many files ( > 5000) and lots of records (aprox 200 mill) so i dont think something like awk would be efficient. 

Id like to load the data first, and then split it in teradata.

Record loaded

C1 C2 C3 C4 C5    C6

1    A    X  3   8   abc{xx};bgh{1,8,5};opr{5,7};pln{abcdsef}

Result expected

C1 C2 C3 C4 C5 C6(abc) C7(bgh) C8(opr)  C9(pln)

1    A   X   3    8       xx       1,8,5      5,7       abcdsef

is there any function to get each part of the split with one read?

Thanks for any suggestions

Tags (3)