SQL function – convert list to table

Here is quick SQL function to convert list to table.

-- sql function to convert list to table
CREATE FUNCTION fn_listtotable
(
	@list nvarchar(max),
	@delimeter nvarchar(100)
)
RETURNS @split TABLE
(
	sno int identity,
	value varchar(max)
)
BEGIN

Declare @xml XML 
select @xml = cast('<A>'+ replace(@list,@delimeter,
	'</A><A>')+ '</A>' as xml)

INSERT INTO @split       
select t.value('.','varchar') as inVal
from @xml.nodes('/A') as x(t)
RETURN
END

The above query exploits the Xquery capabilities of SQL Server to split the delimited values. The function can be used as shown below. There are other methods too however I found this quick and easy.

sql function to convert list to table

Like us on FaceBook Join the fastest growing SQL Server group on FaceBook

 
Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

One Comment on “SQL function – convert list to table”

  1. This only works if the list items are onyl single character strings like A,B,C etc
    It failes when the list items are multiple characters e.g. ABC,DEF,GHI
    In the later case, the returned table has only the 1st character of the list items

Leave a Reply

Your email address will not be published. Required fields are marked *