Tuesday, 31 October 2017

MS SQL function to generate rows using comma seperated string

Below is the MS SQL function to create rows using comma separated string.

   
Create Function [dbo].[SplitExpression]
(
 @Expression Varchar(Max),
 @SplitByChar Char(1)
)
Returns @tbl TABLE (Value varchar(Max))
As
Begin 
 Declare @X xml
 SELECT @X = CONVERT(xml,'<root><s>' + REPLACE(@Expression,@SplitByChar,'</s><s>') 
+ '</s></root>')
 Insert Into @tbl SELECT T.c.value('.','varchar(20)') FROM @X.nodes('/root/s') T(c) 
 Return
End

   Below is the Usage


   
SELECT * FROM [dbo].[SplitExpression]('1,2,3,4,5,6,7,8,9,0',',');




Result in the given screenshot

MS SQL function to generate rows using comma seperated string

Below is the MS SQL function to create rows using comma separated string. Create Function [dbo].[SplitExpression] (  @Expression Varc...