Split strings using tsql – a different method

Goal

To split a string separated by comma or other char using tsql

My way of achieving it

Using dynamic sql

The method that I will describe here is working good enough for the situations in which we want to split a char no longer than 50k chars and it has the benefit of less typing and more intuitive approach but this is only my opinion about it.  Most of the approaches are using CLR, XML, numbers table, CTE and others and are very good explained here (http://sqlperformance.com/2012/07/t-sql-queries/split-strings). The code that I am using to split the string is below

declare @databases nvarchar(max)
set @databases=’a,id,xyz,abcd,abcde,sa,foo,bar,mort,splunge,bacon’
–SET @databases = REPLICATE(@databases, POWER(10, 3));
select @databases=’select ”’+replace(@databases,’,’,”’ union all select ”’)+””
exec(@databases)

If you need to split the string based on a different char then replace comma in the replace function with the new char, for example

select @databases=’select ”’+replace(@databases,’|’,”’ union all select ”’)+””
exec(@databases)

It seems that in the form above the dynamic sql is very dangerous (was made very clear to me here, http://sqlperformance.com/2016/03/sql-server-2016/string-split#comment-121269 )  and I changed it to

select @string ='select '''+replace(replace(@string,'''',''),',',''' union all select ''')+''''

which removes the possibility of running some bad code but it also breaks words like don’t or won’t.

One thought on “Split strings using tsql – a different method”

Leave a Reply

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