Comma delimited string to table
Here I am creating a simple function that converts a comma delimited string to table. It takes 2 arguments. One is the string and second is the delimited character. You can use any character as delimited character.
The function is ready to use. Just copy it and you can directly use it.
/*
The function takes the string and string seperator like ', or | or anything'
and returens a table.
last update: 20-March-2012
by: joydeep das
*/
IF OBJECT_ID (N'fn_CnvtTbl') IS NOT NULL
DROP FUNCTION dbo.fn_CnvtTbl
GO
CREATE FUNCTION fn_CnvtTbl
(
@sStingVal VARCHAR(8000) = NULL,
@sSeperator CHAR(1) = '|'
)
RETURNS
@myTBL TABLE (TblVal VARCHAR(1000))
AS
BEGIN
DECLARE @CurrentStr VARCHAR(2000)
DECLARE @ItemStr VARCHAR(200)
IF ISNULL(@sStingVal,'')='' OR ISNULL(@sSeperator,'')=''
BEGIN
INSERT @myTBL (TblVal) VALUES ('Parameters not properly specified')
RETURN
END
SET @CurrentStr = @sStingVal
WHILE Datalength(@CurrentStr) > 0
BEGIN
IF CHARINDEX(@sSeperator, @CurrentStr,1) > 0
BEGIN
SET @ItemStr = SUBSTRING (@CurrentStr, 1,CHARINDEX(@sSeperator,
@CurrentStr,1) - 1)
SET @CurrentStr = SUBSTRING (@CurrentStr,CHARINDEX(@sSeperator,
@CurrentStr,1) + 1,
(Datalength(@CurrentStr)-
CHARINDEX(@sSeperator,@CurrentStr,1) + 1))
INSERT @myTBL (TblVal) VALUES (@ItemStr)
END
ELSE
BEGIN
INSERT @myTBL (TblVal) VALUES (@CurrentStr)
BREAK;
END
END
RETURN
END
/*
to test the function use
SELECT * FROM dbo.fn_CnvtTbl('RAM,SHAM,JADU,MADHU',',')
*/
Hope you like it.
