Split without delimiter based on position

Question

Split without delimiter based on position

Viewing 6 posts - 31 through 35 (of 35 total)

You must be logged in to reply to this topic. Login to reply

yuvipoy SSChampion Points: 10469 More actions · Answer 1

Hey thanks it works for me

but i am unable to insert more data since @sql is beyond varchar(max) since i am having table with column 122 i am unable to insert after some 80 records.since my input string length is around 3k records i need to split based on it.

Thanks!

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

yuvipoy (10/24/2012)
Hey thanks it works for me
but i am unable to insert more data since @sql is beyond varchar(max) since i am having table with column 122 i am unable to insert after some 80 records.since my input string length is around 3k records i need to split based on it.
Thanks!

Change all the instances of NVARCHAR(whatever) to NVARCHAR(MAX) and CHAR(whatever) to NCHAR(whatever) in my code and try again, please.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

yuvipoy SSChampion Points: 10469 More actions · Answer 3

After Changing to Varchar(max) i am able to insert the data Thanks!

satyasainadh369 SSC Enthusiast Points: 142 More actions · Answer 4

Hi,

Can you please tell me the process that i need to save the split data into multiple columns you have done that. So,please provide me the code to use.

Thanks & Regards,

Sainadh

satyasainadh369 SSC Enthusiast Points: 142 More actions · Answer 5

can you please provide me the code, for splitting string into multiple columns that what you did like shown below, i need the output in the below format.

Rid,col1,col2,col3,col4,col5,col6

1 ,1,2,34,56789,1011,12

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

satyasainadh369 (3/24/2016)
can you please provide me the code, for splitting string into multiple columns that what you did like shown below, i need the output in the below format.
Rid,col1,col2,col3,col4,col5,col6
1 ,1,2,34,56789,1011,12

You should start your own thread instead of hijacking somebody else's as this is a similar yet different topic. Are you saying you have delimited string and you want to split it into columns? This is known as a crosstab or pivot. If you know the number of elements it pretty straight forward. If however the number of elements is dynamic it a little more complex but certainly not impossible. You can read the articles in the my signature about "Cross Tabs and Pivots". The first one is the static version and the second one is the dynamic version. Probably wouldn't hurt to look at the first link in my signature also to read about best practices when posting questions.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/