SQL Basic: Splitter

SQL Basic: Splitter



Create FUNCTION [BSM].[Splitter](@text nvarchar(max), @separator Char(1))
RETURNS @result TABLE (value1 nvarchar(max))
AS
BEGIN
DECLARE @Value1 nVarChar(max)
SELECT @text = CASE WHEN SUBSTRING(@text, 1, 1) = @separator THEN SUBSTRING(@text, 2, LEN(@text)) ELSE @text END

    While PatIndex('%' + @separator +  '%', @text) > 0
Begin
Select @Value1 = lTrim(SubString(@text, 1, PatIndex('%' + @separator +  '%', @text) -1))
        INSERT @result SELECT @Value1 + ':'
        Select @text = SubString(@text, PatIndex('%' + @separator +  '%', @text) +1, Len(@text))
    END
    RETURN
END
GO


CREATE TABLE YourTable
(
PKCol Int,
FieldsChanged nVarChar(Max)
LineCol nVarChar(Max)
)


Insert Into YourTable
Select 1 PKCol, ';LA;LB;LC;' FieldsChanged,
'LA: abc
LB: lmn
LC: xyz
'
LineCol

Select WL.PKCol, s.Value1 Field
,SUBSTRING(CAST(LineCol AS nVARCHAR(MAX))
, PatIndex('%' + s.Value1 + '%', CAST(LineCol AS nVARCHAR(MAX))) + Len(s.Value1) + 1
, (CharIndex(Char(10), CAST(LineCol AS nVARCHAR(MAX)), PatIndex('%' + s.Value1 + '%', CAST(LineCol AS nVARCHAR(MAX))) + Len(s.Value1) + 1))
-(PatIndex('%' + s.Value1 + '%', CAST(LineCol AS nVARCHAR(MAX))) + Len(s.Value1) + 1)
) FieldValue
,WL.ProcessDate
From  YourTable WL
Cross Apply BSM.[Splitter](FieldsChanged, ';') s