Displaying CSV values into rows...

/* This function will takes an input parameter, as a CSV value, and returns a resultset of rows for the CSV values.
**/

-- Method1
If object_id('dbo.tvfCSVToRows') is not null
drop function dbo.tvfCSVToRows
go

create function tvfCSVToRows(@expr1 nvarchar(max))
returns @table table([value] nvarchar(150))
as
begin
declare @targetXml xml

set @expr1 = replace(@expr1,',','</a><a>')
set @expr1 = '<root><a>' + @expr1 + '</a></root>'
select @targetXml = convert(xml,isnull(@expr1,'<root></root>'))
insert into @table
select
lst.itm.value('.','nvarchar(150)')
from @targetXml.nodes('root/a') as lst(itm)
where len(lst.itm.value('.','nvarchar(150)')) != 0

return
end
go


-- Method2
If object_id('dbo.tvfCSVToRows') is not null
drop function dbo.tvfCSVToRows
go

create function dbo.tvfCSVToRows(
@StrngExpr varchar(max),
@RowDelimiter char=','
)
returns @table table(string varchar(1000))
as
begin

declare @string varchar(1000)

-- check for null/empty conditions
if @StrngExpr is null or len(ltrim(rtrim(@StrngExpr))) = 0
return;

if substring(@StrngExpr, len(@StrngExpr), 1) <> @RowDelimiter
set @StrngExpr = @StrngExpr + @RowDelimiter

-- process the string expression.
while charindex(@RowDelimiter , @StrngExpr) >= 1
begin

set @string = substring(@StrngExpr, 1, charindex(@RowDelimiter , @StrngExpr) - 1)
set @StrngExpr = substring(@StrngExpr, charindex(@RowDelimiter , @StrngExpr) + 1, len(@StrngExpr))

insert into @table
select @string

end

return;
end
go