If you have a look at this:
Using comma separated value parameter strings in SQL IN clauses[
^] - it's intended for IN clauses when you use a parameter to pass the comma delimited list, but the function returns a table, so it would be simple for you to modify that to produce your output. You then just SELECT from the table to return the values.
OK...try this:
Create the SQL function:
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[CSVToTableWithId] (@ID INT, @InStr VARCHAR(MAX))
RETURNS @TempTab TABLE
(tempid int IDENTITY(1,1) not null,
sno int not null,
data NVARCHAR(MAX))
AS
BEGIN
;
SET @InStr = REPLACE(@InStr + ',', ',,', ',')
DECLARE @SP INT
DECLARE @VALUE VARCHAR(1000)
WHILE PATINDEX('%,%', @INSTR ) <> 0
BEGIN
SELECT @SP = PATINDEX('%,%',@INSTR)
SELECT @VALUE = LEFT(@INSTR , @SP - 1)
SELECT @INSTR = STUFF(@INSTR, 1, @SP, '')
INSERT INTO @TempTab(sno, data) VALUES (@ID,@VALUE)
END
RETURN
END
GO
Then, use CROSS APPLY to fetch the values:
SELECT ca.sno, ca.data FROM MyTable t
CROSS APPLY (SELECT * FROM [CSVToTableWithId](t.sno, t.data)) ca