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:

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

Follow JasonBussCFX on Twitter

ABOUT THE AUTHOR

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.

    Reply

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!