String Split

Question

String Split

falguni.thakor

SSC Rookie

Points: 48
More actions
November 28, 2012 at 8:34 pm

#270856

Comments posted to this topic are about the item String Split

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

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

frederic.chapelon SSC Eights! Points: 851 More actions · Answer 1

Yeah I used such function a lot in our applications although it is nice to have the ability to call with different delimiters (sometimes people don't like to use commas :-)...)

But recently I have been looking at it from a different point of view by using CLR functions and the speed of it is mind blowing compare to the T-SQL code.

Only limitation though is that the CLR function doesn't accept a string as long as the T-SQL one (or maybe I haven't found the correct configuration yet.

CLR might be very useful in this case so I wanted to give you the code if you want to try it.

Cheers

public partial class UserDefinedFunctions

{

[SqlFunction(Name = "ufn_SplitString", FillRowMethodName = "SplitString_FillRow", TableDefinition = "ID NVARCHAR(255)")]

public static IEnumerable SqlArray(SqlString str, SqlChars delimiter)

{

if (delimiter.Length == 0)

return new string[1] { str.Value };

return str.Value.Split(delimiter[0]);

}

public static void SplitString_FillRow(object row, out SqlString str)

{

str = new SqlString((string)row);

}

};

mstjean Hall of Fame Points: 3310 More actions · Answer 2

Have a look at http://www.sqlservercentral.com/articles/Tally+Table/72993/

(Tally OH! An Improved SQL 8K “CSV Splitter” Function)

Especially, review the discussion thread. You can do this without committing that cardinal sin, looping (see references to the "Church of No RBAR" in the discussion). Performance is much improved when you avoid loops.

Cursors are useful if you don't know SQL

juned.katariya Grasshopper Points: 11 More actions · Answer 3

juned.katariya

Grasshopper

Points: 11

December 11, 2012 at 12:15 am

#1567268

Nice Post:smooooth:, worked like a charm for me 😀

Vinay Kumar SSCertifiable Points: 6099 More actions · Answer 4

You can also do this with help of below script:-

DECLARE @CommString Varchar(100) ='1,2,3,4,5,6,7,8,9,10'

SELECT CP_XML.Node.value('.','Varchar(max)') AS DATA FROM

(

SELECT DataIn_XML = Convert(XML,'<Node>'+REPLACE(@CommString,',','</Node><Node>')+'</Node>')

) AS NowDataIn_XmlFormat

CROSS APPLY DataIN_XML.nodes('Node') CP_XML(Node)

Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 5

akljfhnlaflkj

SSC Guru

Points: 76202

May 3, 2016 at 6:58 am

#1876204

Thanks for the script.