Recently, I worked on an import where I spent a considerable amount of time working out how to effectively parse data from a SQL table, for insertion into another table.

The table I was evaluating basically looked like this:

ID, Value
——————————
“1”, “ABC, DEF, HIJ”
“2”, “KLM, NOP, QRS”
“3”, “TUV, WXYZ”

I needed a row for each distinct value. To parse the data, I created a user defined function with the following code:

CREATE FUNCTION dbo.ParseData
(
    @RowData nvarchar(2000),
    @SplitOn nvarchar(5)
    
)  
RETURNS @RtnValue table
(
    Id int identity(1,1),
    Data nvarchar(100)
)
AS  
BEGIN
    Declare @Cnt int
    Set @Cnt = 1

    While (Charindex(@SplitOn,@RowData)>0)
    Begin
        Insert Into @RtnValue (data)
        Select

            Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

        Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
        Set @Cnt = @Cnt + 1
    End
    
    Insert Into @RtnValue (data)
    Select Data = ltrim(rtrim(@RowData))

    Return
END

Now, this function works great when manually defining the values to parse, such as  select * from dbo.ParseData(‘123,456,123,456’, ‘,’).  This would return

Id, Data
————-
“1”, “123”
“2”, “456”
“3”, “123”
“4”, “456”

as I expected, but when I try to use the function with my original table, I was getting an error:  Cannot find either column “dbo” or the user-defined function or aggregate “dbo.Split”, or the name is ambiguous.

To get around this, I was able to use “Cross Apply” in my sql statement, to run the UDF against each row in my table.  Using Cross Apply result in this statement:

select ID, s.data from [table]
cross apply
dbo.ParseData([Table].Value, ‘,’) s

Which Returns:

Id, Data
————-
“1”, “ABC”
“1”, “DEF”
“1”, “HIJ”
“2”, “KLM”
“2”, “NOP”
“2”, “QRS”
“3”, “TUV”
“3”, “WXYZ”

 Now I have my data parsed with the table ID, ready to be joined to my source data for import.

-Jason