SET ANSI_NULLS ON; SET ANSI_PADDING ON; SET ANSI_WARNINGS ON; SET CONCAT_NULL_YIELDS_NULL ON; SET NUMERIC_ROUNDABORT OFF; SET QUOTED_IDENTIFIER ON; IF OBJECT_ID('[dbo].[FN_SPLIT_INT]') IS NOT NULL DROP FUNCTION [dbo].[FN_SPLIT_INT] GO IF OBJECT_ID('[dbo].[FN_SPLIT]') IS NOT NULL DROP FUNCTION [dbo].[FN_SPLIT] GO CREATE FUNCTION [dbo].[FN_SPLIT] ( @Text nvarchar(max), -- Text to split @Delimiter nvarchar(1000) -- Value to split on, try to only pass a single char. See notes for details. ) /* ------------------------------------------------------------------------------------------------ Purpose: Split text on a common char. Design Notes: 1) Will trim leading/trailing white space from items. 2) Will omit blank and null items. 3) Compatible from SQL 2005 and onward (see details about [item_int] in return table) 4) Return table item is nvarchar(max) (not bound by string length) 5) Reasonably durable with escape sequences, so if a delimiter has a [,%,_ in it, the patter should still match. 6) Uses a sliding search window, not saving the remaining text on each iteration. However, saving each item in a temp variable (@item) was faster than using a CTE to temporarily store the value, which surprised me. 7) Returns the value as an int as well, which is a common use for this function (splitting comma separated lists of ints). Note that this should be low impact in that if you don't query against that column since it is a non-persistent computed column (i.e. low overhead). 8) Supports @Delimiter > 1 char, but slower. Note in the unit tests, with text of approximately 10K, 1 char is about 30% faster, hence the big IF block in the code. Plus, the multi-char delimiter does not scale terribly well. The test with 100,000 records, a 1 char delimiter takes about 6 seconds while with a 5 char delimiter took 430 seconds (7 minutes!). As such, try to replace your multi char delimiters with a single char value before calling this function. Side Note: For what it's worth, I did try an alternative method of saving the remaining "working text" as a sub string of text so the search would get faster near the end, but overall it was slower at about 500 seconds. NOTE: This version does not support case sensitivity. See "TODO" comments if you need a case insensitive version Revision history: --------------------------------------------------------------------------------------------------- Date User Change Description --------------------------------------------------------------------------------------------------- 10/10/2013 Brad Joss (bradjoss@hotmail.com) Initial Public Draft ---------------------------------------------------------------------------------------------------*/ RETURNS @retTable TABLE ( -- Output table definition [item] nvarchar(max) COLLATE DATABASE_DEFAULT NOT NULL, -- Since most of the times we split, we are splitting lists of ints, this makes that process easier. -- Since the column is non persistent it should only be evaluated when requested (low overhead). [item_int] as ( -- SQL 2012 version, better, use if possible -- TRY_CONVERT([int], NULLIF([item],'')) -- SQL 2012 Format, faster and safer, but pre-2012 code provided as well... -- Pre SQL 2012 syntax. Not as reliable, so use 2012 when possible by commenting out this CAST and using the TRY_CONVERT above CAST( CASE WHEN LEN(item) > 11 THEN NULL -- LEN OF (-2147483648) is 11. Simple out of bounds checking. WHEN ISNUMERIC([item]) = 1 AND [item] NOT LIKE '%.%' THEN [item] -- Ensure value conforms to int ELSE null END as int) ) ) WITH SCHEMABINDING AS BEGIN -- Garbage in, Garbage out. If they did not pass input data, return nothing. IF RTRIM(ISNULL(@Text,'')) = '' OR RTRIM(ISNULL(@Delimiter,'')) = '' RETURN DECLARE @ix bigint -- Current index , @pix bigint -- Previous index , @del_len int -- Delimiter length , @text_len bigint -- Input text length , @item nvarchar(max) -- Temp item buffer. I tried w/o using CTEs, but this way was faster SELECT @del_len = LEN(@Delimiter) , @text_len = LEN(@Text) IF @del_len = 1 BEGIN -- CHARINDEX MODE (Much faster than PATINDEX mode) SELECT @ix = CHARINDEX(@Delimiter, @Text) -- TODO: If you want to implment Case Insensitivity here, wrap both in LOWER() , @pix = 0 -- No delim found, just return the passed value, trimmed IF @ix = 0 BEGIN INSERT INTO @retTable(item) SELECT LTRIM(RTRIM(@Text)) -- We know this is not null because of the first GIGO check above END ELSE BEGIN -- Find most of the matches WHILE @ix > 0 BEGIN SELECT -- Get the current value @item = LTRIM(RTRIM(SUBSTRING(@Text,@pix,(@ix - @pix)))) -- Move previous pointer to end of last found delimiter , @pix = @ix + @del_len -- And update the values for next pass though the loop, finding the next match , @ix = CHARINDEX(@Delimiter, @Text, (@ix + @del_len)) -- TODO: If you want to implment Case Insensitivity here, wrap both in LOWER() IF @item <> '' AND @item IS NOT NULL -- Only save non empty values INSERT INTO @retTable(item) VALUES (@item) END -- Get the trailing text SET @item = LTRIM(RTRIM(SUBSTRING(@Text,@pix,@text_len))) IF @item <> '' AND @item IS NOT NULL -- Only save non empty values INSERT INTO @retTable(item) VALUES (@item) END -- @ix = 0 END ELSE -- @del_len = 1 BEGIN -- PATINDEX Mode (SLOW!) Try to pass in text that uses single char delimeters when possible DECLARE @del_pat nvarchar(3002) -- Assume 3x @Delimiter + 2, for escaping every character plus wrapper % -- Escape characters that will mess up the like clause, and wrap in wild cards % SELECT @del_pat = '%' + REPLACE(REPLACE(REPLACE(@Delimiter , '[','[[]') , '%','[%]') , '_', '[_]') + '%' SELECT @ix = PATINDEX(@del_pat, @Text) -- TODO: If you want to implment Case Insensitivity here, wrap both in LOWER() , @pix = 0 -- No delim found, just return the passed value, trimmed IF @ix = 0 BEGIN INSERT INTO @retTable(item) SELECT LTRIM(RTRIM(@Text)) -- We know this is not null because of the first GIGO check above END ELSE BEGIN -- Find most of the matches WHILE @ix > 0 BEGIN SELECT -- Get the curent Item @item = LTRIM(RTRIM(SUBSTRING(@Text,@pix,(@ix - @pix)))) -- Move the previous index to the end of the previous delimiter , @pix = @ix + @del_len -- And set values for next itteration of the loop, finding the next match , @ix = PATINDEX(@del_pat, SUBSTRING(@Text, (@ix + @del_len), @text_len)) -- TODO: If you want to implment Case Insensitivity here, wrap both in LOWER() IF @item <> '' AND @item IS NOT NULL -- Only save non empty values INSERT INTO @retTable(item) VALUES (@item) IF @ix > 0 SET @ix = ((@ix + @pix) - 1) -- -1 since PatIndex is 1 based and Substring is 0 based END -- Get the trailing text SET @item = LTRIM(RTRIM(SUBSTRING(@Text,@pix,@text_len))) IF @item <> '' AND @item IS NOT NULL -- Only save non empty values INSERT INTO @retTable(item) VALUES (@item) END -- @ix = 0 END -- @del_len = 1 RETURN END GO /* Overloaded version to make splitting comma seperated lists of ints easier. Note the delimiter is hard coded to comma and that non-int values will be removed. */ CREATE FUNCTION [dbo].[FN_SPLIT_INT] ( @Text nvarchar(max) -- Text to split ) RETURNS TABLE AS RETURN SELECT [item_int] -- TODO: Optional add distinct? FROM [dbo].[FN_SPLIT](@Text, ',') -- Hard coded to comma delimited WHERE [item_int] IS NOT NULL -- Remove invalid values GO GRANT REFERENCES, SELECT ON [dbo].[FN_SPLIT] TO [public] AS [dbo] GRANT REFERENCES, SELECT ON [dbo].[FN_SPLIT_INT] TO [public] AS [dbo] GO -- Precompile, so performance numbers below are not skewed. SELECT * INTO #Garbage1 FROM [dbo].[FN_SPLIT]('1,2,3',',') SELECT * INTO #Garbage2 FROM [dbo].[FN_SPLIT_INT]('1,2,3') DROP TABLE #Garbage1 DROP TABLE #Garbage2 GO -------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------- -- Unit test -- -- Split has been problematic enough for me I thought these tests important. SET NOCOUNT ON DECLARE @TESTS TABLE ( [cnt] int, [txt] nvarchar(max), [delim] nvarchar(1000), [sum_len] int ) DECLARE @RESULTS TABLE ( [id] int identity(1,1), [msg] varchar(255) null, [pass_fail] AS ( CASE WHEN (ISNULL([expected_count],0) = ISNULL([actual_count],0) AND ISNULL([expected_sum_len],0) = ISNULL([actual_sum_len],0)) THEN 'PASS' ELSE 'FAIL' END ), [runtime] int null, [expected_count] int null, [actual_count] int null, [expected_sum_len] int null, [actual_sum_len] int null, [delim] nvarchar(1000), [txt] nvarchar(max) ) DECLARE @BigText nvarchar(max) DECLARE @BigTextItemCount int DECLARE @BigTextSumHash int -- Alternative large volume tests, set to 10 for quick, set to 100K for a real workout --SELECT @BigTextItemCount = 10, @BigTextSumHash = 11 SELECT @BigTextItemCount = 10000, @BigTextSumHash = 38894 --SELECT @BigTextItemCount = 100000, @BigTextSumHash = 488895 -- Create the hash of big text. I know this code is somewhat ugly, but it creates the large text in -- about 1 second, as opposed to an itterative concat that took 14 minutes... :-) ;with cte as ( select 9 as [i] union all select [i] - 1 FROM cte where [i] > 0 ), crs as ( SELECT ROW_NUMBER() OVER(ORDER BY c1.[i]) as [rn] FROM cte c1 -- 10 CROSS JOIN cte c2 -- 100 CROSS JOIN cte c3 -- 1000 CROSS JOIN cte c4 -- 10000 CROSS JOIN cte c5 -- 100000 ) SELECT @BigText = ( ( SELECT '#' + CAST([rn] as nvarchar(32)) FROM crs WHERE [rn] <= @BigTextItemCount FOR XML PATH('') , TYPE ).value('.', 'nvarchar(max)') ) -- Most of the tests go here -- INSERT INTO @TESTS (cnt, sum_len, txt, delim) -- Basic 1-char Delim Tests SELECT 0, 0, '', ',' UNION ALL SELECT 0, 0, null, ',' UNION ALL SELECT 0, 0, 'a', null UNION ALL SELECT 0, 0, 'a', '' UNION ALL SELECT 3, 3, '1,2,3', ',' UNION ALL SELECT 3, 3, ',1,2,3', ',' UNION ALL SELECT 3, 3, '1,2,3,', ',' UNION ALL SELECT 3, 3, ',1,2,3,', ',' UNION ALL SELECT 3, 3, ' , 1 , 2 , 3 , ', ',' UNION ALL SELECT 3, 3, ',,, , 1 , 2 , 3 , ,,,', ',' UNION ALL SELECT 3, 3, 'a, b, c', ',' UNION ALL SELECT 3, 3, 'a,b,c', ',' UNION ALL SELECT 2, 6, 'Cat=Pub', '=' UNION ALL SELECT 1, 1, 'a', ',' UNION ALL SELECT 1, 1, ' a ', ',' -- 1 char Int Tests UNION ALL SELECT 10, 18, 'a,1,2,-1,-2,b,1.0,-1.0, 3 , -4 ,', ',' -- Basic multi-char delim tests UNION ALL SELECT 0, 0, '', '' UNION ALL SELECT 0, 0, null, '' UNION ALL SELECT 0, 0, 'a', null UNION ALL SELECT 0, 0, 'a', '' UNION ALL SELECT 3, 3, '123', '' -- Case Insensitivity test 1 UNION ALL SELECT 3, 3, '123', '' -- Case Insensitivity test 2 UNION ALL SELECT 3, 3, '123', '' UNION ALL SELECT 3, 3, '123', '' UNION ALL SELECT 3, 3, ' 1 2 3 ', '' UNION ALL SELECT 3, 3, ' 1 2 3 ', '' UNION ALL SELECT 3, 3, 'a b c', '' UNION ALL SELECT 3, 3, 'abc', '' UNION ALL SELECT 2, 6, 'CatPub', '' UNION ALL SELECT 1, 1, 'a', '' UNION ALL SELECT 1, 1, ' a ', '' -- multi char delim Int Tests UNION ALL SELECT 10, 18, 'a12-1-2b1.0-1.0 3 -4 ', '' -- Delims with escape char % in it UNION ALL SELECT 0, 0, '', '' UNION ALL SELECT 0, 0, null, '' UNION ALL SELECT 0, 0, 'a', null UNION ALL SELECT 0, 0, 'a', '' UNION ALL SELECT 3, 3, '123', '' UNION ALL SELECT 3, 3, '123', '' UNION ALL SELECT 3, 3, '123', '' UNION ALL SELECT 3, 3, '123', '' UNION ALL SELECT 3, 3, ' 1 2 3 ', '' UNION ALL SELECT 3, 3, ' 1 2 3 ', '' UNION ALL SELECT 3, 3, 'a b c', '' UNION ALL SELECT 3, 3, 'abc', '' UNION ALL SELECT 2, 6, 'CatPub', '' UNION ALL SELECT 1, 1, 'a', '' UNION ALL SELECT 1, 1, ' a ', '' UNION ALL SELECT 10, 18, 'a12-1-2b1.0-1.0 3 -4 ', '' -- Delims with escape char _ in it UNION ALL SELECT 0, 0, '', '' UNION ALL SELECT 0, 0, null, '' UNION ALL SELECT 0, 0, 'a', null UNION ALL SELECT 0, 0, 'a', '' UNION ALL SELECT 3, 3, '123', '' UNION ALL SELECT 3, 3, '123', '' UNION ALL SELECT 3, 3, '123', '' UNION ALL SELECT 3, 3, '123', '' UNION ALL SELECT 3, 3, ' 1 2 3 ', '' UNION ALL SELECT 3, 3, ' 1 2 3 ', '' UNION ALL SELECT 3, 3, 'a b c', '' UNION ALL SELECT 3, 3, 'abc', '' UNION ALL SELECT 2, 6, 'CatPub', '' UNION ALL SELECT 1, 1, 'a', '' UNION ALL SELECT 1, 1, ' a ', '' UNION ALL SELECT 10, 18, 'a12-1-2b1.0-1.0 3 -4 ', '' -- Semi Evil tests UNION ALL SELECT 2, 2, 'a~`!@#$%^&*()_+|-=\{}:;"''<>,.?/ b', '~`!@#$%^&*()_+|-=\{}:;"''<>,.?/ ' -- no [] UNION ALL SELECT 2, 2, 'a~`!@#$%^&*()_+|-=\{}[]:;"''<>,.?/ b', '~`!@#$%^&*()_+|-=\{}[]:;"''<>,.?/ ' -- with [] UNION ALL SELECT 2, 2, 'a' + CHAR(10) + CHAR(13) + 'b', CHAR(10) + CHAR(13) -- White space chars -- Big Text Tests UNION ALL SELECT @BigTextItemCount,@BigTextSumHash,@BigText, '#' UNION ALL SELECT @BigTextItemCount,@BigTextSumHash,REPLACE(@BigText,'#', ''), '' -- Loop through each of the tests, logging results DECLARE @txt nvarchar(max) -- Input text DECLARE @delim nvarchar(1000) -- Input delimiter DECLARE @cnt int -- Expected count DECLARE @sum_len int -- Expected sum(len(item)) DECLARE @t_cnt int -- Actual count DECLARE @t_sum_len int -- Actual sum(len(item)) DECLARE @start datetime -- Test Start time (for performance tracking) DECLARE cur CURSOR FAST_FORWARD FOR SELECT [cnt],[txt],[delim],[sum_len] FROM @TESTS OPEN cur FETCH cur INTO @cnt, @txt, @delim,@sum_len WHILE @@FETCH_STATUS = 0 BEGIN SELECT @start = GetDate(); -- Execute test SELECT @t_cnt = count(*), @t_sum_len = SUM(LEN(item)) FROM [dbo].[FN_SPLIT](@txt, @delim) -- Log results INSERT INTO @RESULTS ([msg],[runtime],[expected_count],[actual_count],[expected_sum_len],[actual_sum_len],[delim],[txt]) SELECT 'TEST', DATEDIFF(ms, @start, GetDate()), @cnt, @t_cnt, @sum_len, ISNULL(@t_sum_len,0), @delim, @txt FETCH cur INTO @cnt, @txt, @delim,@sum_len END CLOSE cur DEALLOCATE cur ---------------------------------------------------------------------------------------------------------------------------------- -- Extra tests that required additional coding DECLARE @int_test nvarchar(max) SELECT @int_test = N'a,1,2,-1,-2,b,1.0,-1.0, 3 , -4 ,' -- Basic int test, ensure int's are properly returned INSERT INTO @RESULTS ([msg],[runtime],[expected_count],[actual_count],[expected_sum_len],[actual_sum_len],[delim],[txt]) SELECT 'Tested Ints 1', null, 6, count(*), null, null, ',', @int_test FROM [dbo].[FN_SPLIT](@int_test, ',') WHERE [item_int] is not null -- Ensure text value associated with int values maps 1:1 INSERT INTO @RESULTS ([msg],[runtime],[expected_count],[actual_count],[expected_sum_len],[actual_sum_len],[delim],[txt]) SELECT 'Tested Ints 2', null, 6, count(*), null, null, ',', @int_test FROM [dbo].[FN_SPLIT](@int_test, ',') WHERE CAST([item_int] as nvarchar(max)) = [item] and item_int is not null -- Split int tests SELECT @int_test = '1,-2,3' SELECT @start = GetDate(); INSERT INTO @RESULTS ([msg],[runtime],[expected_count],[actual_count],[expected_sum_len],[actual_sum_len],[delim],[txt]) SELECT 'Split Int: ' + @int_test, DATEDIFF(ms, @start, GetDate()), 3, count(*), 2, SUM(item_int), '#', @int_test FROM [dbo].[FN_SPLIT_INT](@int_test) SELECT @int_test = '1,a,-2,b,3,c' SELECT @start = GetDate(); INSERT INTO @RESULTS ([msg],[runtime],[expected_count],[actual_count],[expected_sum_len],[actual_sum_len],[delim],[txt]) SELECT 'Split Int: ' + @int_test, DATEDIFF(ms, @start, GetDate()), 3, count(*), 2, SUM(item_int), '#', @int_test FROM [dbo].[FN_SPLIT_INT](@int_test) SELECT @int_test = '1, -2, 3' -- Spaces between commas SELECT @start = GetDate(); INSERT INTO @RESULTS ([msg],[runtime],[expected_count],[actual_count],[expected_sum_len],[actual_sum_len],[delim],[txt]) SELECT 'Split Int: ' + @int_test, DATEDIFF(ms, @start, GetDate()), 3, count(*), 2, SUM(item_int), '#', @int_test FROM [dbo].[FN_SPLIT_INT](@int_test) SELECT @int_test = ' 1, -2, 3 ' -- Leading/trailing SELECT @start = GetDate(); INSERT INTO @RESULTS ([msg],[runtime],[expected_count],[actual_count],[expected_sum_len],[actual_sum_len],[delim],[txt]) SELECT 'Split Int: ' + @int_test, DATEDIFF(ms, @start, GetDate()), 3, count(*), 2, SUM(item_int), '#', @int_test FROM [dbo].[FN_SPLIT_INT](@int_test) SELECT @int_test = '999999999999999,1,-2,-3,-99999999999999999' -- Basic boundry testing SELECT @start = GetDate(); INSERT INTO @RESULTS ([msg],[runtime],[expected_count],[actual_count],[expected_sum_len],[actual_sum_len],[delim],[txt]) SELECT 'Split Int: ' + @int_test, DATEDIFF(ms, @start, GetDate()), 3, count(*), -4, SUM(item_int), '#', @int_test FROM [dbo].[FN_SPLIT_INT](@int_test) SELECT @int_test = ' 1.0, -2.0, 3 ' -- Should only return ints SELECT @start = GetDate(); INSERT INTO @RESULTS ([msg],[runtime],[expected_count],[actual_count],[expected_sum_len],[actual_sum_len],[delim],[txt]) SELECT 'Split Int: ' + @int_test, DATEDIFF(ms, @start, GetDate()), 1, count(*), 3, SUM(item_int), '#', @int_test FROM [dbo].[FN_SPLIT_INT](@int_test) ---------------------------------------------------------------------------------------------------------------------------------- -- Runtime / Performance testing IF OBJECT_ID('tempdb..#t1') IS NOT NULL DROP TABLE #t1 IF OBJECT_ID('tempdb..#t2') IS NOT NULL DROP TABLE #t2 IF OBJECT_ID('tempdb..#t3') IS NOT NULL DROP TABLE #t3 SELECT @start = GetDate(); SELECT [item] INTO #t1 FROM [dbo].[FN_SPLIT](@BigText, '#') INSERT INTO @RESULTS ([msg],[runtime],[expected_count],[actual_count],[expected_sum_len],[actual_sum_len],[delim],[txt]) SELECT 'Speed 1: Item only', DATEDIFF(ms, @start, GetDate()), null, null, null, null, '#', @BigText SELECT @start = GetDate(); SELECT [item_int] INTO #t3 FROM [dbo].[FN_SPLIT](@BigText, '#') INSERT INTO @RESULTS ([msg],[runtime],[expected_count],[actual_count],[expected_sum_len],[actual_sum_len],[delim],[txt]) SELECT 'Speed 2: Item Int Only', DATEDIFF(ms, @start, GetDate()), null, null, null, null, '#', @BigText SELECT @start = GetDate(); SELECT [item] INTO #t2 FROM [dbo].[FN_SPLIT](@BigText, '#') WHERE [item_int] IS NOT NULL INSERT INTO @RESULTS ([msg],[runtime],[expected_count],[actual_count],[expected_sum_len],[actual_sum_len],[delim],[txt]) SELECT 'Speed 3: Item With Int Filter', DATEDIFF(ms, @start, GetDate()), null, null, null, null, '#', @BigText IF OBJECT_ID('tempdb..#t1') IS NOT NULL DROP TABLE #t1 IF OBJECT_ID('tempdb..#t2') IS NOT NULL DROP TABLE #t2 IF OBJECT_ID('tempdb..#t3') IS NOT NULL DROP TABLE #t3 ---------------------------------------------------------------------------------------------------------------------------------- /* -- Ensure test failures work INSERT INTO @RESULTS ([msg],[runtime],[expected_count],[actual_count],[expected_sum_len],[actual_sum_len],[delim],[txt]) SELECT 'INTENTIONAL FAILURE', null, 1, 2, 3, 4, '', '' */ -- Display results SELECT * FROM @RESULTS ORDER BY CASE [pass_fail] WHEN 'FAIL' THEN 0 ELSE 1 END ASC, [id] ASC -- And Total runtime SELECT SUM(ISNULL(runtime,0)) as [total_runtime] FROM @RESULTS -- Raise errors as needed. IF (SELECT count(*) FROM @RESULTS WHERE [pass_fail] = 'FAIL') > 0 RAISERROR('Unexpected results. Review results table for details.',18,1) GO