Split using any delimiter for SQL Server

Recently, I had need to split a database string using a multi-character delimiter: each “row” in the string had “<?>” instead of a comma, tab, or other separator. Modified an old split function to return a table of results and use any delimiter.

SplitAny()

create function dbo.SplitAny (
  @inputstring nvarchar(max),
  @delimiter   varchar(8000)
)
returns @items table (
  item nvarchar(max)
)
as
begin;

declare @item nvarchar(max)
declare @itemlist nvarchar(max)
declare @delimindex bigint

if (@delimiter is null or @delimiter = '')
begin;
  set @delimiter = ',';
end;
set @itemlist = @inputstring;
set @delimindex = charindex(@delimiter, @itemlist, 0);
while (@delimindex != 0)
begin;
  set @item = substring(@itemlist, 0, @delimindex)
  insert into @items (item) values (@item);
  set @itemlist = substring(@itemlist, (@delimindex + len(@delimiter)), (len(@itemlist) - @delimindex));
  set @delimindex = charindex(@delimiter, @itemlist, 0);
end;
/* At least one delimiter found in the input string. */
if @item is not null
begin;
  set @item = @itemlist;
  insert into @items (item) values (@item);
end;
/* Delimiter was not found, send back input string. */
else
begin;
  insert into @items (item) values (@inputstring);
end;
return;

end

go