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
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.
Posted by: MR. JOYDEEP DAS
give this one a try...
ReplyDeletecreate function dbo.fnSplit
( @list varchar(max)
, @delimiter varchar(10) )
returns table
/*
————————————————————————————————————————————————————————————————————————————————————————————————————
Purpose : Divides the @list parameter at the @delimiter into an enumerated table.
Parameters : @list - the delimited list of values up to 8000 characters long
@delimeter - the delimter value
Calling : select Value, RowId from dbo.fnSplit(@list, @delimeter);
Returns : Table
History :
Date Developer Wk Itm Description
—————————— —————————————————— —————— ————————————————————————————————————————————————————————————
12/08/2011 Paul Hunter 0 Initial function creation
————————————————————————————————————————————————————————————————————————————————————————————————————
*/
return( select cast(v2.l.value('.', 'varchar(8000)') as varchar(8000)) as Value
, row_number() over ( order by r.row ) as RowId
from( select( select convert(xml, ''
+ replace(@list, @delimiter, '')
+ '') ) ) as v1(x)
cross apply x.nodes('/l/v') v2(l)
cross apply ( select 1 as row ) r );
go
Thanks "Anonymous"
Deletethis is the one I use. it creates a tvf. go to the link, for an explanation. it is extremely fast and efficient.
ReplyDelete--http://www.sqlservercentral.com/articles/Tally+Table/72993/
-- Jeff Moden
CREATE FUNCTION dbo.udfDelimitedSplit8K
--===== Define I/O parameters
(@pString VARCHAR(8000),
@pDelimiter CHAR(1))
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 0 up to 10,000...
-- enough to cover VARCHAR(8000)
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS (--==== This provides the "zero base" and limits the number of rows right up front
-- for both a performance gain and prevention of accidental "overruns"
SELECT 0 UNION ALL
SELECT TOP (DATALENGTH(ISNULL(@pString,1))) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
SELECT t.N+1
FROM cteTally t
WHERE (SUBSTRING(@pString,t.N,1) = @pDelimiter OR t.N = 0)
)
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for
--===== the final element when no delimiter is found.
SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY s.N1),
Item = SUBSTRING(@pString,s.N1,ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000))
FROM cteStart s
;
GO
Thanks Again...
Delete