Using SQL User defined functions and Cross Apply to parse SQL data

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:

    @RowData nvarchar(2000),
    @SplitOn nvarchar(5)
RETURNS @RtnValue table
    Id int identity(1,1),
    Data nvarchar(100)
    Declare @Cnt int
    Set @Cnt = 1

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

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

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


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, 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.


Follow JasonBussCFX on Twitter


Jason Buss

Jason is a senior application developer with Customer FX.

1 Comment

  1. Just what I was looking for. Perfectly clear example on how to get the results I need. Most of my other Google hits were a bit too wordy and didn’t give a short and sweet description and example.

    Thanks Jason.


Submit a Comment

Your email address will not be published.

Subscribe To Our Newsletter

Join our mailing list to receive the latest Infor CRM (Saleslogix) and Creatio (bpm'online) news and product updates!

You have Successfully Subscribed!