Splitting Strings in TSql using CTE

There are number of occasions where we need to split a string to individual values,one such common use  case is for multivalue parameters  in ssrs, when a stored procedure  is used  to  feed the dataset   we need to split the    string parameter  values  .


This can be implemented in a number of ways, i chooose to use a cte  and than embedd that cte in a table valued function,   and this use case  only  works  in sql server 2012 , for other versions, replace the  conditional IIF function with a case


Consider  u   got string values of countries as shown below

Declare @sp VarChar(max)='UK,USA,FRANCE,CANADA,MEXICO,BRAZIL,CHINA, MALAYSIA,TURKEY,INDIA,AUSTRALIA'


and we want to split it into individual countries ,to split the string, whatever logic we use, we need to know the  postion of  individual string  i.e the starting position and ending position of the sorrounded delimiters .

This is where recursive cte  can be very usefull,the way it is done is we use Charecter index   string function in the anchor query to get the first  end position of a delimiter   of first individual string   and 0 as the starting position, as there will be no delimeters at the start of string.

Than we use these values recursivly to get the  next delimeter position ,and to do that we  increment the previous  delimeter postion in the charecter index function start value,  in this case ,after the anchor query gets 3 as the first delimeter value  than it is incremented by 1 i.e   4 and this 4 will become the string starting  position for the second individual string i.e USA  and the charecter index gets the  next individual ending  delimeter position as 7 ,and this logic is repeated  recursively to build the starting and ending delimeter position for each string as shown in the second figure.


;With Cte
As
(

Select   CHARINDEX(',',@sp,0)  tt, cast (0  as int) t
Union ALL
Select  CHARINDEX(',',@sp,tt+1),  cast(tt as int) as t      from  Cte
where  tt>0
)

Select    SUBSTRING(@sp,IIF(t=0,1,t+1),IIF(tt=0,len(@sp)-t,tt-t-1) )  from Cte

The above cte gets the delimited index values  recursivly    for each individual   string    as shown below

Once we  have these values ,the next statement
Select    SUBSTRING(@sp,IIF(t=0,1,t+1),IIF(tt=0,len(@sp)-t,tt-t-1) )  from Cte

simply selects the values between those delimeters
as shown below

 we can embedd this logic into a table valued function as shown below and subsequently use this function in the stored procedure  as shown in the query following this function


 Create   Function apn_split(@Sp varchar(max))

 Returns Table

 As
 Return
WIth Cte
as
(

Select   CHARINDEX(',',@sp,0)  tt, cast (0  as int) t
Union ALL
Select  CHARINDEX(',',@sp,tt+1),  cast(tt as int) as t      from  Cte
where  tt>0
)

Select    SUBSTRING(@sp,iif(t=0,1,t+1),iif(tt=0,len(@sp)-t,tt-t-1) )  as Country  from Cte



Consider to build to report to analyse sales data (in this example  i'm using AdwentureWorksDW2008R2) which will  allow the end user to  select multiple countries    as show below

Than you can use the above function in the stored procedure to pass  multiple values from ssrs to stored procedure  as shown below

Alter Procedure ap_ProductSalesByCountry

@Country varchar(max)
As
SELECT     DimProduct.EnglishProductName, DimProductSubcategory.EnglishProductSubcategoryName, DimProductCategory.EnglishProductCategoryName,
                      DimSalesTerritory.SalesTerritoryCountry, DimDate.EnglishMonthName, DimDate.CalendarYear, FactResellerSales.SalesAmount
FROM         FactResellerSales INNER JOIN
                      DimSalesTerritory ON FactResellerSales.SalesTerritoryKey = DimSalesTerritory.SalesTerritoryKey INNER JOIN
                      DimProduct ON FactResellerSales.ProductKey = DimProduct.ProductKey INNER JOIN
                      DimProductSubcategory ON DimProduct.ProductSubcategoryKey = DimProductSubcategory.ProductSubcategoryKey INNER JOIN
                      DimProductCategory ON DimProductSubcategory.ProductCategoryKey = DimProductCategory.ProductCategoryKey INNER JOIN
                      DimDate ON FactResellerSales.OrderDateKey = DimDate.DateKey
WHERE     DimSalesTerritory.SalesTerritoryCountry  in ( Select distinct country    from apn_split(@Country))