CLR Funtion To split the cama separated string.

Question

CLR Funtion To split the cama separated string.

Tony1234

Right there with Babe

Points: 723
More actions
September 25, 2012 at 12:32 am

#282043

Hi All,
I need a CLR function to split the "," separated string.
Ex: string like 1,2,3,4,
Result:
Value
----------
1
2
3
4
Thank’q

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

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

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

Why do you need this to be in a CLR function?

There is a very good T-SQL function which you can find in my signature post by Jeff, but for ease of use here is the link http://www.sqlservercentral.com/articles/Tally+Table/72993/

Tony1234 Right there with Babe Points: 723 More actions · Answer 2

I thought CLR function gives better performance on string functions, so that I am looking for CLR function.

laurie-789651 SSCertifiable Points: 7680 More actions · Answer 3

Try this:

http://sqlblog.com/blogs/adam_machanic/archive/2009/04/28/sqlclr-string-splitting-part-2-even-faster-even-more-scalable.aspx

Ant-Green SSC Guru Points: 113507 More actions · Answer 4

If something can be done in SQL do it in SQL not CLR is my mantra.

String splitting can be done in either format, just depends on the load your putting through the splitter, there is a cut off point as with anything where performance vs values starts to go astray, the article details that at the bottom.

Have to remember that CLR uses memory from outside of the buffer pool, so if you already have a heavily loaded memory utilisation on the server, CLR may not be the best way to go.

Depends on the trade offs that you want.

Another one to look at http://www.pawlowski.cz/2010/10/fastest-csv-strings-splitting-using-clr-t-sql-vs-clr-revisited/

dmiyake@ascendantsmg.com Newbie Points: 7 More actions · Answer 5

It's from a few years ago, but I've found Erland Sommarskog's entry on this to be the most comprehensive:

http://www.sommarskog.se/arrays-in-sql-2005.html#Split

We ended up using a modified version of his CLR function in our application. Sped things up a lot.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

You will also find a clr string splitter in the discussion thread of Jeff's article linked above in a previous post. IIRC it is based on Adam Machanic's work.