Code Project

Link Unit

Wednesday, July 23, 2014

Easy way to split string

We generally use function to split a delimited string into rows. The code for same is listed below
CREATE FUNCTION
[dbo].[fnSplitString] ( @string NVARCHAR(MAX) ,@delimiter CHAR(1) ) RETURNS@output
TABLE (splitdata NVARCHAR(MAX)) BEGIN DECLARE @start INT ,@end INT SELECT @start = 1 ,@end = CHARINDEX(@delimiter, @string) WHILE @start < LEN(@string) + 1 BEGIN IF @end = 0 SET @end = LEN(@string) + 1 INSERT INTO @output (splitdata) VALUES (SUBSTRING(@string, @start, @end - @start)) SET @start = @end + 1 SET @end = CHARINDEX(@delimiter, @string, @start) END RETURN END GO We will be using XQuery method to split the string in easy and efficient way. Following steps are taken to split the string. #1. Convert the string into formatted XML,
for that replace the delimitor with any tag (used in example). #2. Remove empty nodes like '' or '' #3. list value of all nodes USE tempdb DECLARE @x VARCHAR(MAX) DECLARE @x1 XML DECLARE @t1 DATETIME DECLARE @t2 DATETIME SET @x = '100|101|102|' SET @x = replicate(@x, 1) SELECT @x1 = cast
(
replace
(
replace
(
'<x>' + replace
(
@x, '|', '</x><x>')
+ '</x>', '<x ></x>', '')
, '<x></x>', '')
AS XML)
SET @t1 = getdate() -- start time of process SELECT X.x1.value('.', 'varchar(max)') FROM @x1.nodes('//x') X(x1) -- XQuery SET @t2 = getdate() -- End time of process SELECT datediff(ms, @t1, @t2) -- Find time difference in milliseconds SET @t1 = getdate() -- start time of process SELECT * FROM [dbo].[fnSplitString](@x, '|') SET @t2 = getdate() -- End time of process SELECT datediff(ms, @t1, @t2) Benchmarks for XQuery method to split the string -- XQuery -- 0ms for 3 -- 0ms for 30 -- 6ms for 300 -- 36ms for 3000 -- 350ms for 30000 -- 7s for 300000 Benchmarks for Procedure method to split the string -- 3ms for 3 -- 10ms for 30 -- 100ms for 300 -- Procedure 1m for 3000 -- Procedure 11s for 30000 -- Procedure 2m15s for 300000
Conclusion: Looking at performance and simplicity of XQuery, I will recommend it over procedural way. Hope it helps.