SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Simple and efficient way to split string with seperator


Simple and efficient way to split string with seperator

Author
Message
stevenzrao
stevenzrao
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 92
Comments posted to this topic are about the item Simple and efficient way to split string with seperator
Filipe
Filipe
SSC Veteran
SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)

Group: General Forum Members
Points: 208 Visits: 351
Very elegant solution, but I have just 2 concerns.
One is that the definition of the string is varchar(8000), but the master table has values only up to 2047.
The other I'm not sure if is really a problem, but with the lack of an order by you could get results out of order. Then again, maybe the onus of including an order by should be on the code calling this function.
Anyway, I liked the solution. Thanks for posting.
stevenzrao
stevenzrao
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 92
Hi Filipe,
Thank you very much for your comment.
1) You are right, this is the limitation and the input string @string should be of type varchar(2047) instead of varchar(8000). If the string length is bigger than 2047, we will have to setup a number list table in advance. But the idea will be similar. Also this same idea can be used to efficiently split a string column of a table and the length of string from most tables are most likely less than 2047.
2) I believe the "order by" statement is not needed since it will just follow the order of table master..spt_values. At least that is what I have been noticed so far.
ChrisCarsonSQL
ChrisCarsonSQL
SSC-Enthusiastic
SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)

Group: General Forum Members
Points: 167 Visits: 485
I applaud the effort, except I have to ask why this function would be preferable to the following code:

Jeff Moden's CSV splitter

I have searched many resources, and it seems like this splitter function is pretty close to a "gold standard" for splitter functions using T-SQL.

I would recommend that function to anyone seeking a fast and efficient way to split strings.

Had you reviewed that article before writing yours?

_____________________________

Past performance != future results.
All opinions and suggestions are my own, unless they're really good. Then I most likely read them here...
stevenzrao
stevenzrao
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 92
I did not know about the article you mentioned. I was trying to find a solution to split a string without looping and also want to split string from a large table. Even this funciton can be called with a string parameter, I find it is much more very useful and handy to use this idea to split a field from a table without using a splitting string function with looping. It is also more efficient this way.
Iwas Bornready
Iwas Bornready
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13678 Visits: 885
Thanks for the script.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search