Scenerio : we have coma sperated data and want it in tabular form

Input: @strinput Varchar=’1,2,3,4′

Output :

Solution

Create a function as below

CREATE FUNCTION [dbo].[SplitString]
(
@Input NVARCHAR(MAX),
@Character CHAR(1)
)
RETURNS @Output TABLE (
Item NVARCHAR(1000)
)
AS
BEGIN
DECLARE @StartIndex INT, @EndIndex INT

SET @StartIndex = 1
IF SUBSTRING(@Input, LEN(@Input) – 1, LEN(@Input)) <> @Character
BEGIN
SET @Input = @Input + @Character
END

WHILE CHARINDEX(@Character, @Input) > 0
BEGIN
SET @EndIndex = CHARINDEX(@Character, @Input)

INSERT INTO @Output(Item)
SELECT SUBSTRING(@Input, @StartIndex, @EndIndex – 1)

SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))
END

RETURN
END

Now use below statement to get desired output –

Select * from [dbo].[SplitString](‘1,2,3′,’,’)