Independent Software Developer

SOLVED: Massive Data – How split single text with delimiter or multiline from column/cell into multiple rows

How split single text with delimiter or multiline from column/cell into multiple rows? In case of massive data, the performance is very important and has high priority, so we focus on solution which we can use in production environment.
The solution bases on created function on MS SQL server, presented below on attached screen:

CREATE FUNCTION dbo.fn_SplitToDelimitedList
(
@String NVARCHAR(MAX),
@Delimiter VARCHAR(10) = NULL
)
RETURNS @SplittedValues TABLE(
Value NVARCHAR(MAX)
)
AS
BEGIN
DECLARE @SplitLength INT

SET @Delimiter = IIF(@delimiter is NULL,',',@delimiter)

WHILE @String IS NOT NULL AND len(@String) > 0
BEGIN
SELECT @SplitLength = (CASE charindex(@Delimiter, @String)
WHEN 0 THEN
datalength(@String) / 2
ELSE
charindex(@Delimiter, @String) - 1
END)

INSERT INTO @SplittedValues
SELECT cast(substring(@String, 1, @SplitLength) AS NVARCHAR(MAX))
WHERE
ltrim(rtrim(isnull(substring(@String, 1, @SplitLength), ''))) <> '';

SELECT @String = (CASE ((datalength(@String) / 2) - @SplitLength)
WHEN 0 THEN
''
ELSE
right(@String, (datalength(@String) / 2) - @SplitLength - 1)
END)

END

RETURN

END

Syntax is following:
fn_SplitToDelimitedList(param1,param2)
where
param1 – string or name of column, delimited by different separators
param2 – delimiter e.g ‘,’, ‘:’, CHAR(10)

For instance:

a)

SELECT t.*,c.Value as Splitted_Value
FROM Product t
CROSS APPLY fn_SplitToDelimitedList('1002,2,3',',') c

where param1 is string and param2 is delimiter ‘,’

b)

SELECT t.*,c.Value as Splitted_Value
FROM Product t
CROSS APPLY fn_SplitToDelimitedList(t.Note,CHAR(10)) c

where param1 is name of column and param2 is delimiter CHAR(10) – new line.

c)

SELECT t.*,c.Value as Splitted_Value
FROM Product t
INNER JOIN (SELECT CAT.CategoryId,c.Value FROM TRANSACTION_CATEGORIES CAT CROSS APPLY fn_SplitToDelimitedList(CAT.Note,CHAR(10)) c) T2 On T2.TransactionId = T.TransactionId

where param1 is name of column and param2 is delimiter CHAR(10) – new line, with narrow down the search

Works FINE on SQL SERVER on PRODUCTION ENV!!!