split string into mulitiple columns

Question

Hello all,
I have a table where one column contains cap(^)delimited string
ID vlaue_string
1 Naveen^kumar^address a^address b^chennai^TN^560033
2 Vimal^Murugesan^address x^address y^chennai2^TN^560063
3 Robert^Thangavel^address z^address c^chennai5^TN^5604444

There is maximum number of tokens in the string - n

I need to split each string and put every token in its respective column for further manipulation:

   Col1    Col2    Col3      Col4          Col5    Col6  col7
1 Naveen   Kumar   Address a  address b   chennai   TN   56033
2 Vimal  Murugesan address x   address y   chennai2  TN   560063
3 Robert Thangavel address z address c    chennai5  TN   5604444

Now my requirment is only for 7 tokens but in future i will use it dynamically.and iam going to use this data in multiple places so i want it as a function.or tell me any other better way.

Kindly help me pls
Thanks in advance

Answer 1

0

As a break from suggesting the @JeffModen solution, here is one from Brad Schultz - http://bradsruminations.blogspot.com/2009/10/un-making-list-or-shredding-of-evidence.html
http://bradsruminations.blogspot.com/2009/10/making-list-and-checking-it-twice.html.

You can take column values into a string and take a string and split it into columns/rows.

I havent tested it but if you are working with lots of records you may want to test this and the Tally Table method to ensure you pick the most performant

answered Mar 25 '11 at 01:28 AM

Fatherjack ♦♦
38.8k ● 55 ● 69 ● 104

pls can you explain me clearly

Mar 25 '11 at 01:40 AM bindumadhavi

Definitely do some performance testing if you're working with lots, or you're not doing this as a one-off.

Mar 25 '11 at 01:41 AM ThomasRushton ♦

have you read the details at the links?

Mar 25 '11 at 01:44 AM Fatherjack ♦♦

Hang on - the question's tagged plsql...

Mar 25 '11 at 01:48 AM ThomasRushton ♦

yes but iam not able to understand

Mar 25 '11 at 01:56 AM bindumadhavi

(comments are locked)

split string into mulitiple columns

Follow this question

Related Questions