I came across an interesting problem the other day, I needed to do some string manipulation with SQL Server, normally dealing with this sort of stuff sucks, I wanted to split a string like ‘item1, item1, item3’ .. so splitting on a given character. The first thing that popped to my mind was a Table Valued function, something like this:
-- Splits a column based on a specified delimiter
CREATE FUNCTION dbo.Split
(
@List nvarchar(2000),
@SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(
Id int identity(1,1),
Value nvarchar(2000)
)
AS
BEGIN
While (Charindex(@SplitOn,@List)>0)
Begin
Insert Into @RtnValue (value)
Select Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))
Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
End
Insert Into @RtnValue (Value)
Select Value = ltrim(rtrim(@List))
Return
END
There are many examples of this type of function on web, my point isn’t the actual implementation, rather the way you can use it.
I can now run the query:
select Value from dbo.Split('item1, item2, item3',',')
But my problem was a little different from the trivial example above, I wanted to pass data into this function that was the result of my query, rather than just passing arguments in statically like above and using the function as a table, I really wanted to apply the function to the data from my table, on a row by row level.
After a little searching I found the CROSS APPLY SQL syntax, with this I can now do exactly what I want:
select SF.Value from TestTable TT
cross apply dbo.Split(TT.Value, ',') SF
The above query simple selects the data from the TestTable, then cross apply’s the Split function passing it the TestTable’s value column (which contains the data ‘Item1, Item2, Item3, Item4’, also note that I’m selecting the column from the Split function.
This Produces the desired results:
Given a table like:
And data like:
I thought this was pretty cool. SQL can be fun.