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!!!