Splitting a string after the first space after the 10th character?

Question

Splitting a string after the first space after the 10th character?

shan-422658

SSC Enthusiast

Points: 130
More actions
April 16, 2018 at 7:17 pm

#403268

How can I split a string first space after the 10th character?
For example: The sentence is 'Apple is Red and Grapes are Purple'
The 10th character is R. So string 1 should be 'Apple is Red' (The first space after R )
Then String 2 should be 'and Grapes are Purple'

Viewing 14 posts - 1 through 13 (of 13 total)

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

saravanatn SSCarpal Tunnel Points: 4533 More actions · Answer 1

shan-422658 - Monday, April 16, 2018 7:17 PM
How can I split a string first space after the 10th character?
For example: The sentence is 'Apple is Red and Grapes are Purple'
The 10th character is R. So string 1 should be 'Apple is Red' (The first space after R )
Then String 2 should be 'and Grapes are Purple'

SELECT Substring(words_split, 1, Charindex(' ', words_split, 10)) string1, Substring(words_split, Charindex(' ', words_split, 10) + 1, Len( words_split)) AS string2 FROM words;

Saravanan

ScottPletcher SSC Guru Points: 101105 More actions · Answer 2

SELECT string, LEFT(string, split_byte - 1) AS string1, SUBSTRING(string, split_byte + 1, 8000) AS string2 FROM ( VALUES('Apple is Red and Grapes are Purple'), ('Thisisonereallylongsentencewithnospaces.') ) AS test_data(string) CROSS APPLY ( SELECT CHARINDEX(' ', string + ' ', 11) AS split_byte ) AS alias1

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

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

shan-422658 - Monday, April 16, 2018 7:17 PM
How can I split a string first space after the 10th character?
For example: The sentence is 'Apple is Red and Grapes are Purple'
The 10th character is R. So string 1 should be 'Apple is Red' (The first space after R )
Then String 2 should be 'and Grapes are Purple'

Are you really wanting to have an unknown number of columns? Meaning that if you had a really long bunch of words it would separate lines after words for every 10 characters? Or do you just want the 2 columns?

_______________________________________________________________

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/

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

Sean Lange - Tuesday, April 17, 2018 10:26 AM
shan-422658 - Monday, April 16, 2018 7:17 PM
How can I split a string first space after the 10th character?
For example: The sentence is 'Apple is Red and Grapes are Purple'
The 10th character is R. So string 1 should be 'Apple is Red' (The first space after R )
Then String 2 should be 'and Grapes are Purple'
Are you really wanting to have an unknown number of columns? Meaning that if you had a really long bunch of words it would separate lines after words for every 10 characters? Or do you just want the 2 columns?

This sounds like homework or an interview question to demonstrate the knowledge on the CHARINDEX function and its third parameter.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

sgmunson SSC Guru Points: 110640 More actions · Answer 5

Sean Lange - Tuesday, April 17, 2018 10:26 AM
shan-422658 - Monday, April 16, 2018 7:17 PM
How can I split a string first space after the 10th character?
For example: The sentence is 'Apple is Red and Grapes are Purple'
The 10th character is R. So string 1 should be 'Apple is Red' (The first space after R )
Then String 2 should be 'and Grapes are Purple'
Are you really wanting to have an unknown number of columns? Meaning that if you had a really long bunch of words it would separate lines after words for every 10 characters? Or do you just want the 2 columns?

As no mention was made of further splitting beyond the use of the first space beyond the 10th character, I can't even come up with a reason to do that without using rows instead of columns. I'm also pretty sure it's a good idea NOT to give people bad ideas....

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

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

sgmunson - Thursday, April 19, 2018 12:28 PM
Sean Lange - Tuesday, April 17, 2018 10:26 AM
shan-422658 - Monday, April 16, 2018 7:17 PM
How can I split a string first space after the 10th character?
For example: The sentence is 'Apple is Red and Grapes are Purple'
The 10th character is R. So string 1 should be 'Apple is Red' (The first space after R )
Then String 2 should be 'and Grapes are Purple'
Are you really wanting to have an unknown number of columns? Meaning that if you had a really long bunch of words it would separate lines after words for every 10 characters? Or do you just want the 2 columns?
As no mention was made of further splitting beyond the use of the first space beyond the 10th character, I can't even come up with a reason to do that without using rows instead of columns. I'm also pretty sure it's a good idea NOT to give people bad ideas....

I wasn't giving them a bad idea. I was simply trying to clarify the question. Splitting strings on words after the 10th character is totally bizarre requirement on its own. I can't come up with a reason that makes any sense at all.

_______________________________________________________________

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/

sgmunson SSC Guru Points: 110640 More actions · Answer 7

Sean Lange - Thursday, April 19, 2018 12:37 PM
sgmunson - Thursday, April 19, 2018 12:28 PM
Sean Lange - Tuesday, April 17, 2018 10:26 AM
shan-422658 - Monday, April 16, 2018 7:17 PM
How can I split a string first space after the 10th character?
For example: The sentence is 'Apple is Red and Grapes are Purple'
The 10th character is R. So string 1 should be 'Apple is Red' (The first space after R )
Then String 2 should be 'and Grapes are Purple'
Are you really wanting to have an unknown number of columns? Meaning that if you had a really long bunch of words it would separate lines after words for every 10 characters? Or do you just want the 2 columns?
As no mention was made of further splitting beyond the use of the first space beyond the 10th character, I can't even come up with a reason to do that without using rows instead of columns. I'm also pretty sure it's a good idea NOT to give people bad ideas....
I wasn't giving them a bad idea. I was simply trying to clarify the question. Splitting strings on words after the 10th character is totally bizarre requirement on its own. I can't come up with a reason that makes any sense at all.

As to the split after the 10th character being bizarre, totally agree, but the question stands... why make it any worse by repeating that same sin throughout the rest of the string? That's what I was referring to as "giving them a bad idea"... I try pretty hard not to give folks the idea that their bizarre concoction ought to be made even more bizarre...

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

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

sgmunson - Thursday, April 19, 2018 12:56 PM
Sean Lange - Thursday, April 19, 2018 12:37 PM
sgmunson - Thursday, April 19, 2018 12:28 PM
Sean Lange - Tuesday, April 17, 2018 10:26 AM
shan-422658 - Monday, April 16, 2018 7:17 PM
How can I split a string first space after the 10th character?
For example: The sentence is 'Apple is Red and Grapes are Purple'
The 10th character is R. So string 1 should be 'Apple is Red' (The first space after R )
Then String 2 should be 'and Grapes are Purple'
Are you really wanting to have an unknown number of columns? Meaning that if you had a really long bunch of words it would separate lines after words for every 10 characters? Or do you just want the 2 columns?
As no mention was made of further splitting beyond the use of the first space beyond the 10th character, I can't even come up with a reason to do that without using rows instead of columns. I'm also pretty sure it's a good idea NOT to give people bad ideas....
I wasn't giving them a bad idea. I was simply trying to clarify the question. Splitting strings on words after the 10th character is totally bizarre requirement on its own. I can't come up with a reason that makes any sense at all.
As to the split after the 10th character being bizarre, totally agree, but the question stands... why make it any worse by repeating that same sin throughout the rest of the string? That's what I was referring to as "giving them a bad idea"... I try pretty hard not to give folks the idea that their bizarre concoction ought to be made even more bizarre...

Don't disagree. But what I wanted to avoid was the "well your code works to get the first split but I need to split on every 10 characters again for the whole thing". Have seen way too many questions blossom into a total rewrite of the logic because the whole question was not asked up front. I would hate to answer X when they want Y. As in a simple split being coded but what they really want is a dynamic cross tab. Really don't get why I would be reprimanded for asking for clarity.

_______________________________________________________________

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/

Luis Cazares SSC Guru Points: 183706 More actions · Answer 9

Sean Lange - Thursday, April 19, 2018 12:37 PM
... I can't come up with a reason that makes any sense at all.

I came up with one (and posted it). I can't see any other.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

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

Luis Cazares - Friday, April 20, 2018 9:46 AM
Sean Lange - Thursday, April 19, 2018 12:37 PM
... I can't come up with a reason that makes any sense at all.
I came up with one (and posted it). I can't see any other.

Yes as a knowledge test is about the only time anything so contrived would be useful. I totally agree!!!

_______________________________________________________________

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/

sgmunson SSC Guru Points: 110640 More actions · Answer 11

Sean Lange - Thursday, April 19, 2018 2:12 PM
sgmunson - Thursday, April 19, 2018 12:56 PM
Sean Lange - Thursday, April 19, 2018 12:37 PM
sgmunson - Thursday, April 19, 2018 12:28 PM
Sean Lange - Tuesday, April 17, 2018 10:26 AM
shan-422658 - Monday, April 16, 2018 7:17 PM
How can I split a string first space after the 10th character?
For example: The sentence is 'Apple is Red and Grapes are Purple'
The 10th character is R. So string 1 should be 'Apple is Red' (The first space after R )
Then String 2 should be 'and Grapes are Purple'
Are you really wanting to have an unknown number of columns? Meaning that if you had a really long bunch of words it would separate lines after words for every 10 characters? Or do you just want the 2 columns?
As no mention was made of further splitting beyond the use of the first space beyond the 10th character, I can't even come up with a reason to do that without using rows instead of columns. I'm also pretty sure it's a good idea NOT to give people bad ideas....
I wasn't giving them a bad idea. I was simply trying to clarify the question. Splitting strings on words after the 10th character is totally bizarre requirement on its own. I can't come up with a reason that makes any sense at all.
As to the split after the 10th character being bizarre, totally agree, but the question stands... why make it any worse by repeating that same sin throughout the rest of the string? That's what I was referring to as "giving them a bad idea"... I try pretty hard not to give folks the idea that their bizarre concoction ought to be made even more bizarre...
Don't disagree. But what I wanted to avoid was the "well your code works to get the first split but I need to split on every 10 characters again for the whole thing". Have seen way too many questions blossom into a total rewrite of the logic because the whole question was not asked up front. I would hate to answer X when they want Y. As in a simple split being coded but what they really want is a dynamic cross tab. Really don't get why I would be reprimanded for asking for clarity.

Wasn't really a reprimand, but think of it this way... you CAN NOT POSSIBLY ensure that you won't get the truth AFTER you code something. That is an unrealistic expectation, and human nature just isn't as good as we'd all like. So why suggest that they get even further from a good solution, when we're the folks that should be recommending good ones? I hear ya on trying to get clarity, but usually, the best thing is to just ask for more details, as such things often only leak out slowly... if we all start suggesting moving away from good solutions, eventually, we won't be able to offer good ones.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

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

sgmunson - Friday, April 20, 2018 10:41 AM
Sean Lange - Thursday, April 19, 2018 2:12 PM
sgmunson - Thursday, April 19, 2018 12:56 PM
Sean Lange - Thursday, April 19, 2018 12:37 PM
sgmunson - Thursday, April 19, 2018 12:28 PM
Sean Lange - Tuesday, April 17, 2018 10:26 AM
shan-422658 - Monday, April 16, 2018 7:17 PM
How can I split a string first space after the 10th character?
For example: The sentence is 'Apple is Red and Grapes are Purple'
The 10th character is R. So string 1 should be 'Apple is Red' (The first space after R )
Then String 2 should be 'and Grapes are Purple'
Are you really wanting to have an unknown number of columns? Meaning that if you had a really long bunch of words it would separate lines after words for every 10 characters? Or do you just want the 2 columns?
As no mention was made of further splitting beyond the use of the first space beyond the 10th character, I can't even come up with a reason to do that without using rows instead of columns. I'm also pretty sure it's a good idea NOT to give people bad ideas....
I wasn't giving them a bad idea. I was simply trying to clarify the question. Splitting strings on words after the 10th character is totally bizarre requirement on its own. I can't come up with a reason that makes any sense at all.
As to the split after the 10th character being bizarre, totally agree, but the question stands... why make it any worse by repeating that same sin throughout the rest of the string? That's what I was referring to as "giving them a bad idea"... I try pretty hard not to give folks the idea that their bizarre concoction ought to be made even more bizarre...
Don't disagree. But what I wanted to avoid was the "well your code works to get the first split but I need to split on every 10 characters again for the whole thing". Have seen way too many questions blossom into a total rewrite of the logic because the whole question was not asked up front. I would hate to answer X when they want Y. As in a simple split being coded but what they really want is a dynamic cross tab. Really don't get why I would be reprimanded for asking for clarity.
Wasn't really a reprimand, but think of it this way... you CAN NOT POSSIBLY ensure that you won't get the truth AFTER you code something. That is an unrealistic expectation, and human nature just isn't as good as we'd all like. So why suggest that they get even further from a good solution, when we're the folks that should be recommending good ones? I hear ya on trying to get clarity, but usually, the best thing is to just ask for more details, as such things often only leak out slowly... if we all start suggesting moving away from good solutions, eventually, we won't be able to offer good ones.

I don't get it. I was asking for clarity, not suggesting they take some other path. Whatever, makes no difference really. We all agree that the very premise of this is pretty ridiculous. I think that Luis' idea is the only one that makes much sense at all. :hehe:

_______________________________________________________________

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/

sgmunson SSC Guru Points: 110640 More actions · Answer 13

Sean Lange - Friday, April 20, 2018 10:52 AM
sgmunson - Friday, April 20, 2018 10:41 AM
Sean Lange - Thursday, April 19, 2018 2:12 PM
sgmunson - Thursday, April 19, 2018 12:56 PM
Sean Lange - Thursday, April 19, 2018 12:37 PM
sgmunson - Thursday, April 19, 2018 12:28 PM
Sean Lange - Tuesday, April 17, 2018 10:26 AM
shan-422658 - Monday, April 16, 2018 7:17 PM
How can I split a string first space after the 10th character?
For example: The sentence is 'Apple is Red and Grapes are Purple'
The 10th character is R. So string 1 should be 'Apple is Red' (The first space after R )
Then String 2 should be 'and Grapes are Purple'
Are you really wanting to have an unknown number of columns? Meaning that if you had a really long bunch of words it would separate lines after words for every 10 characters? Or do you just want the 2 columns?
As no mention was made of further splitting beyond the use of the first space beyond the 10th character, I can't even come up with a reason to do that without using rows instead of columns. I'm also pretty sure it's a good idea NOT to give people bad ideas....
I wasn't giving them a bad idea. I was simply trying to clarify the question. Splitting strings on words after the 10th character is totally bizarre requirement on its own. I can't come up with a reason that makes any sense at all.
As to the split after the 10th character being bizarre, totally agree, but the question stands... why make it any worse by repeating that same sin throughout the rest of the string? That's what I was referring to as "giving them a bad idea"... I try pretty hard not to give folks the idea that their bizarre concoction ought to be made even more bizarre...
Don't disagree. But what I wanted to avoid was the "well your code works to get the first split but I need to split on every 10 characters again for the whole thing". Have seen way too many questions blossom into a total rewrite of the logic because the whole question was not asked up front. I would hate to answer X when they want Y. As in a simple split being coded but what they really want is a dynamic cross tab. Really don't get why I would be reprimanded for asking for clarity.
Wasn't really a reprimand, but think of it this way... you CAN NOT POSSIBLY ensure that you won't get the truth AFTER you code something. That is an unrealistic expectation, and human nature just isn't as good as we'd all like. So why suggest that they get even further from a good solution, when we're the folks that should be recommending good ones? I hear ya on trying to get clarity, but usually, the best thing is to just ask for more details, as such things often only leak out slowly... if we all start suggesting moving away from good solutions, eventually, we won't be able to offer good ones.
I don't get it. I was asking for clarity, not suggesting they take some other path. Whatever, makes no difference really. We all agree that the very premise of this is pretty ridiculous. I think that Luis' idea is the only one that makes much sense at all. :hehe:

Heh, if it weren't for differences in perspective, we'd all be the same, and life would be boring... Totally agree that the premise is goof-ball...

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)