Split without delimiter based on postion into multiple columns

Question

Split without delimiter based on postion into multiple columns

satyasainadh369

SSC Enthusiast

Points: 142
More actions
March 24, 2016 at 4:47 am

#325103

I am having a query i need to split the query based on the position i specify
Say
string is 12345678910111213141516........
i am having position as 1,1,2,5,4,2
i need as
Rid,col1,col2,col3,col4,col5,col6
1 ,1,2,34,56789,1011,12
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

satyasainadh369 (3/24/2016)
I am having a query i need to split the query based on the position i specify
Say
string is 12345678910111213141516........
i am having position as 1,1,2,5,4,2
i need as
Rid,col1,col2,col3,col4,col5,col6
1 ,1,2,34,56789,1011,12
Thanks

Your requirement looks to me to be the same or similar to the one described in this thread, which was solved many years ago. If your requirement is different, then please explain very clearly why.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Alan Burstein SSC Guru Points: 61141 More actions · Answer 2

I was curious and looked at the thread.

ChrisM@home (10/22/2012)
yuvipoy (10/22/2012)
Ok, thanks. Can you give an example of how the data from this output table would be used please?
ChrisM as i have said i am having some business logic's on it i can't reveal on forums.
No problem, I'm fully qualified to work with really secret stuff.

This cracked me up!

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Alan.B (3/24/2016)
I was curious and looked at the thread.
ChrisM@home (10/22/2012)
yuvipoy (10/22/2012)
Ok, thanks. Can you give an example of how the data from this output table would be used please?
ChrisM as i have said i am having some business logic's on it i can't reveal on forums.
No problem, I'm fully qualified to work with really secret stuff.
This cracked me up!

Yeah and the OP missed it!

Missed an opportunity too. It was a small enough project to tackle at home. I was sc-cleared at the time, working with military telephony stuff, and quite happy to sign an nda.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Ed Wagner SSC Guru Points: 287026 More actions · Answer 4

ChrisM@Work (3/24/2016)
Alan.B (3/24/2016)
I was curious and looked at the thread.
ChrisM@home (10/22/2012)
yuvipoy (10/22/2012)
Ok, thanks. Can you give an example of how the data from this output table would be used please?
ChrisM as i have said i am having some business logic's on it i can't reveal on forums.
No problem, I'm fully qualified to work with really secret stuff.
This cracked me up!
Yeah and the OP missed it!
Missed an opportunity too. It was a small enough project to tackle at home. I was sc-cleared at the time, working with military telephony stuff, and quite happy to sign an nda.

This is classic. Chris, if I ever get some really secret stuff (as opposed to just normal secret stuff) and need help with it, I'll know who to call. 😀

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Ed Wagner (3/24/2016)
ChrisM@Work (3/24/2016)
Alan.B (3/24/2016)
I was curious and looked at the thread.
ChrisM@home (10/22/2012)
yuvipoy (10/22/2012)
Ok, thanks. Can you give an example of how the data from this output table would be used please?
ChrisM as i have said i am having some business logic's on it i can't reveal on forums.
No problem, I'm fully qualified to work with really secret stuff.
This cracked me up!
Yeah and the OP missed it!
Missed an opportunity too. It was a small enough project to tackle at home. I was sc-cleared at the time, working with military telephony stuff, and quite happy to sign an nda.
This is classic. Chris, if I ever get some really secret stuff (as opposed to just normal secret stuff) and need help with it, I'll know who to call. 😀

Sure! We're not afraid of no ghosts 😉

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden