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

if object_id('tempdb..#Input') is not null
drop table #Input

create table #Input
(
Part int,
Specs varchar(50)
)

insert #Input
select 123, 'D-dfldkk; P-4987843; D48974587' union all
select 456, 'A-dfldkk; Z-4987843'

--The above is your table

--To avoid the performance issues with using replace while calling the DelimitedSplit8K function
--we can do this with a cte

;with cte as
(
select Part, Replace(Specs, '; ', ';') as Specs
from #Input
)

--Now we just need to retrieve the data
select cte.Part, s.Item as Specs
from cte
cross apply dbo.DelimitedSplit8k(cte.Specs, ';') s

ahpitre (2/18/2013)

THis function works great, but, has the following limitations or things that can be improved :

1) If the string to be splitted has the delimiter character at the end, then, it returns an additional item/ItemNumber row where item will be empty. For example :

Searching for ; on a string that contains 'ddkfjdkdjkdfkdjl;1111;' will result in :

ItemNumber Item
------------------------------------------
1 ddkfjdkdjkdfkdjl
2 1111
3

So, function needs to be fixed to not return the last item if it will result in an empty item (NULL or '').

2) I managed to put all my delimiters on a table, then, CROSS Join my main table to the Delimiters table. The result allows you to dynamically insert the character being searched for, and, searching for multiple characters without having to hard code them on the SQL command. An example of my 1st step prior to using the delimiter8k function :

SELECT * FROM dbo.tblDelimiters CROSS JOIN
dbo.qryDoc_Ref_Specs_to_Doc_Controlling_Parts
WHERE (dbo.qryDoc_Ref_Specs_to_Doc_Controlling_Parts.[Reference Specs] LIKE N'%' + dbo.tblDelimiters.delimiter)

I then use the above results as my data source for the CROS APPLY query with the delimited8K function. My query then looks like this :

SELECT DISTINCT *
FROM [ESM].[dbo].[qryDoc_Ref_Specs_to_Doc_Controlling_Parts_delimited_Ref_Specs] CROSS APPLY dbo.DelimitedSplit8K([Reference Specs],
[Reference Specs delimiter])

CREATE FUNCTION [dbo].[DelimitedSplit8KB]
--===== Define I/O parameters
(@pString VARCHAR(8000) , @pDelimiter CHAR(1))
--WARNING!!! DO NOT USE MAX DATA-TYPES HERE! IT WILL KILL PERFORMANCE!
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000...
-- enough to cover VARCHAR(8000)
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
-- for both a performance gain and prevention of accidental "overruns"
SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
SELECT 1 UNION ALL
SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) COLLATE Latin1_General_BIN = @pDelimiter COLLATE Latin1_General_BIN
),
cteLen(N1,L1) AS(--==== Return start and length (for use in substring)
SELECT s.N1,
ISNULL(NULLIF(CHARINDEX(@pDelimiter COLLATE Latin1_General_BIN,@pString COLLATE Latin1_General_BIN,s.N1) ,0)-s.N1,8000)
FROM cteStart s
)
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
Item = SUBSTRING(@pString, l.N1, l.L1)
FROM cteLen l
;