split string into mulitiple columns

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

more ▼

asked Mar 25, 2011 at 01:27 AM in Default

avatar image

1 1 1 2

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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

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

more ▼

answered Mar 25, 2011 at 01:28 AM

avatar image

Fatherjack ♦♦
43.8k 79 101 118

pls can you explain me clearly

Mar 25, 2011 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, 2011 at 01:41 AM ThomasRushton ♦♦

have you read the details at the links?

Mar 25, 2011 at 01:44 AM Fatherjack ♦♦

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

Mar 25, 2011 at 01:48 AM ThomasRushton ♦♦

yes but iam not able to understand

Mar 25, 2011 at 01:56 AM bindumadhavi
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Mar 25, 2011 at 01:27 AM

Seen: 3079 times

Last Updated: Mar 25, 2011 at 01:33 AM

Copyright 2018 Redgate Software. Privacy Policy