CLR Funtion To split the cama separated string.

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » CLR Funtion To split the cama separated...

CLR Funtion To split the cama separated string.

Rate Topic

Display Mode

Topic Options

Author

Message

Tony1234

Posted Tuesday, September 25, 2012 12:32 AM

Grasshopper

Group: General Forum Members
Last Login: Sunday, May 12, 2013 9:57 PM
Points: 14, Visits: 94

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

Post #1363808

anthony.green

Posted Tuesday, September 25, 2012 1:42 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

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/

Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1363834

Tony1234

Posted Tuesday, September 25, 2012 4:02 AM

Grasshopper

Group: General Forum Members
Last Login: Sunday, May 12, 2013 9:57 PM
Points: 14, Visits: 94

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

Post #1363901

laurie-789651

Posted Tuesday, September 25, 2012 4:04 AM

SSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 11:28 AM
Points: 274, Visits: 783

Try this:

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

Post #1363907

anthony.green

Posted Tuesday, September 25, 2012 4:05 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

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/

Post #1363909

dmiyake

Posted Wednesday, September 26, 2012 6:56 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, March 14, 2013 11:17 AM
Points: 1, Visits: 47

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.

Post #1364632

Lynn Pettis

Posted Wednesday, September 26, 2012 7:25 AM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 12:02 AM
Points: 21,596, Visits: 27,415

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.

Cool

Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1364660

« Prev Topic | Next Topic »

Permissions