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 = REPLICATE(@databases, POWER(10, 3));
select @databases=’select ”’+replace(@databases,’,’,”’ union all select ”’)+””
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 ”’)+””
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.