-- Drop function if it already exists
IF EXISTS (
SELECT *
FROM sys.all_objects
WHERE type = N'TF'
AND name = N'fn_CSVToTableString'
)
DROP FUNCTION dbo.fn_CSVToTableString
GO
CREATE FUNCTION dbo.fn_CSVToTableString (@stringarray nvarchar(max))
RETURNS @table TABLE
(
string nvarchar(128) not null
)
AS
BEGIN
begin
declare @ptr int, @nextptr int, @length int
select @ptr = 0, @nextptr = 1
while @nextptr > 0
begin
select @nextptr = charindex(',', @stringarray, @ptr + 1)
select @length = case when @nextptr > 0
then @nextptr
else len(@stringarray) + 1
end - @ptr - 1
insert @table (string)
values (rtrim(ltrim(convert(nvarchar(128), substring(@stringarray, @ptr + 1, @length)))))
select @ptr = @nextptr
end