In this post, I'm trying to explain you how one parse the comma separated string to a table so that which can be used as any other regular table.
This would work only SQL Server 2005 Onwards releases as I'm creating table valued function to accomplish this.
I have created a function called [dbo].[GetDelimitedStringToTable] which returns the table having two columns ( 1 for Index and 1 for value).
Please find the definition for the function [dbo].[GetDelimitedStringToTable]
/**********************************************************************
Object Name :
GetDelimitedStringToTable
Purpose : Splits the given input delimited string
and returns a table.
Returns 2 columns
ID
- Represents the IDENTITY column.
Value
- Represents the data of a delimited string.
Author : Karthik D V
Created Date:
06-Jun-2011
Sample Calls:
SELECT * FROM
[dbo].[GetDelimitedStringToTable] ( '1,2,3,4', DEFAULT )
SELECT * FROM
[dbo].[GetDelimitedStringToTable] ( 'Karthik#Aneesh#Anand#Senthil#Niloy - The
Doc#Prasob - Super Singer', '#' )
Modification
History:
----------------------------------------------------------------------
Date Modified By Modification Details
----------------------------------------------------------------------
----------------------------------------------------------------------
**********************************************************************/
CREATE FUNCTION [dbo].[GetDelimitedStringToTable]
(
@DelimitedString VARCHAR(8000) ,
@Delimiter CHAR(1) = ','
)
RETURNS
@ParsedTable TABLE(ID
INT IDENTITY(0,1), Value VARCHAR(MAX))
BEGIN
;WITH
CTE ( StartPos, EndPos ) AS
(
SELECT 1, CHARINDEX(@Delimiter,
@DelimitedString)
UNION ALL
SELECT
EndPos + (LEN(@Delimiter)),
CHARINDEX(@Delimiter, @DelimitedString,
EndPos
+ (DATALENGTH(@Delimiter)))
FROM CTE
WHERE EndPos > 0
)
INSERT INTO @ParsedTable ( Value)
SELECT
Value = SUBSTRING ( @DelimitedString,
StartPos, CASE WHEN EndPos > 0 THEN EndPos -
StartPos ELSE 4000 END
)
FROM CTE
OPTION (MAXRECURSION 32767)
RETURN
END
GO
Now, lets look at some samples:
SELECT * FROM [dbo].[GetDelimitedStringToTable] ( 'Karthik#Aneesh#Anand#Senthil#Niloy - The Doc#Prasob - Super Singer', '#' )
SELECT * FROM [dbo].[GetDelimitedStringToTable]
( '1,2,3,4', DEFAULT )