Splitting csv to columns

Suppose you have string in csv format and want to split into seperate columns. You can use parsename function as long as you have maximum four values. The following would work for any number of values

declare @s varchar(2000),@data varchar(2000)

select @s='this,is,test'

select @data=''''+replace(@s,',',''',''')+''''

exec('select '+@data)

Published 15 March 2008 10:57 by Madhivanan
Filed under: , , ,

Comments

# re: Splitting csv to columns

17 March 2008 08:47 by Hugo Kornelis

But remember that this is a VERY dangerous technique, since you're opening yourself wide to SQL injection. As this slightly modified version proves:

declare @s varchar(2000),@data varchar(2000)

select @s='this,is,SQL injection''; drop table HopeThisDoesntReallyExist; --'

select @data=''''+replace(@s,',',''',''')+''''

exec('select '+@data)

Best, Hugo

# re: Splitting csv to columns

17 March 2008 14:31 by Madhivanan

Hi Hugo

Thanks

I should have specified about SQL Injection Smile

# Set based splitting of delimited strings to columns

01 April 2008 13:23 by SimonS Blog on SQL Server Stuff

I recently presented on using CLR in SQL at DevWeek and commented on how TSQL Udfs just don't perform