The most concise way to convert a CSV row to a table in TSQL?

Question

-- Given a CSV string like this: declare @roles varchar(800) select @roles = 'Pub,RegUser,ServiceAdmin' -- Question: How to get roles into a table view like this: select 'Pub' union select 'RegUser' union select 'ServiceAdmin' 

---

After posting this question, I started playing with some kind of dynamic SQL. This seems to work, but it looks like there might be some security risks using dynamic SQL thoughts about this?

 declare @rolesSql varchar(800) select @rolesSql = 'select ''' + replace(@roles, ',', ''' union select ''') + '''' exec(@rolesSql) 

Answer 1

See my answer from here.

But basically you would:

Create this function in your database:

 CREATE FUNCTION dbo.Split(@origString varchar(max), @Delimiter char(1)) returns @temptable TABLE (items varchar(max)) as begin declare @idx int declare @split varchar(max) select @idx = 1 if len(@origString )<1 or @origString is null return while @idx!= 0 begin set @idx = charindex(@Delimiter,@origString) if @idx!=0 set @split= left(@origString,@idx - 1) else set @split= @origString if(len(@split)>0) insert into @temptable(Items) values(@split) set @origString= right(@origString,len(@origString) - @idx) if len(@origString) = 0 break end return end 

and then call the function and pass the string you want to split.

 Select * From dbo.Split(@roles, ',') 

Answer 2

If you are working with SQL Server 130 compatibility level, then the STRING_SPLIT function is now the shortest method available.

Link Link: https://msdn.microsoft.com/en-gb/library/mt684588.aspx

Using:

 SELECT * FROM string_split('Pub,RegUser,ServiceAdmin',',') RESULT: value ----------- Pub RegUser ServiceAdmin 

Answer 3

Your options are discussed in detail here:

• Arrays and Lists in SQL Server

Answer 4

Using BULK INSERT, you can import a CSV file into your sql table -

http://blog.sqlauthority.com/2008/02/06/sql-server-import-csv-file-into-sql-server-using-bulk-insert-load-comma-delimited-file-into-sql- server /

Answer 5

Using SQL Server built into XML parsing is also an option. Of course, this overshadows all the nuances of a CSV compatible with RFC-4180.

 -- Given a CSV string like this: declare @roles varchar(800) select @roles = 'Pub,RegUser,ServiceAdmin' -- Here the XML way select split.csv.value('.', 'varchar(100)') as value from ( select cast('<x>' + replace(@roles, ',', '</x><x>') + '</x>' as xml) as data ) as csv cross apply data.nodes('/x') as split(csv) 

If you are using SQL 2016+, it is better to use string_split , but this is the usual way to do this before SQL 2016.