Cross Apply – Yes, you can use a table-valued function without a cursor

I ran into a scenario where i was informed that “Table-valued functions are a pain to use because you need to use a cursor”. The problem was that the individual had a blob of text that they wished to break up using a set delimiter, and they wished to use TSQL. Since I usually reply to questions with an answer, and a “also remove the cursor, please”, developers have become accustomed to writing queries without cursors. So, I suppose this was a topic that needed to get some attention at some point.

The answer to this dilemma is that you certainly can use a UDF to break up the blob of text and return the information without employing the use of a cursor. To do this, we can use CROSS APPLY. This will break up the data and return the relevant rows for us.

First, we create a UDF, in this case, a splitter function.  There are examples of these everywhere, I threw this one together quickly.  Pass in some text and a single character delimiter, get back the split up results – you get the idea.

create function [dbo].[MySplitter]
	(@Text nvarchar(max), @Delimiter char(1))
returns @Table table(ordinal int identity(1,1), data nvarchar(500))
as
    begin

		declare @working_text nvarchar(max) = @text
		declare @token nvarchar(max) = null
		declare @next_delimiter_pos int = 0

		set @next_delimiter_pos = charindex(@delimiter, @working_text)

		while (@next_delimiter_pos != 0)
		begin
			set @token = ltrim(left(@working_text, @next_delimiter_pos - 1))
			set @working_text = right(@working_text, len(@working_text) - @next_delimiter_pos)

			insert into @table(data)
			values(@token)

			set @next_delimiter_pos = charindex(@delimiter, @working_text)
		end

		if(@next_delimiter_pos = 0 and len(@working_text) > 0)
		begin
			insert into @table(data)
			select ltrim(@working_text)
		end

    return
end

Now, we can use this UDF to demonstrate how data can be returned using CROSS APPLY:

declare @t table(textblob nvarchar(100))

insert into @t
values
       ('Hello - Goodbye'),
       ('Yesterday - Today - Tomorrow')

select
       t.textblob,
       s.ordinal,
       s.data
from @t t
cross apply dbo.mysplitter(t.textblob,'-') s

Results:

textblob

ordinal

data

Hello – Goodbye

1

Hello

Hello – Goodbye

2

Goodbye

Yesterday – Today – Tomorrow

1

Yesterday

Yesterday – Today – Tomorrow

2

Today

Yesterday – Today – Tomorrow

3

Tomorrow

In this case, we have at least one value returned for each of the text blobs, so we will not have any NULL data sets returned from the UDF. If we were expecting NULLs, we would use OUTER APPLY instead of CROSS APPLY.

Advertisements