x

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 '11 at 01:27 AM in Default

bindumadhavi gravatar image

bindumadhavi
1 1 1 2

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

1 answer: sort newest

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
more ▼

answered Mar 25 '11 at 01:28 AM

Fatherjack gravatar image

Fatherjack ♦♦
41.2k 72 77 107

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)
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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x50
x34
x5

asked: Mar 25 '11 at 01:27 AM

Seen: 2494 times

Last Updated: Mar 25 '11 at 01:33 AM