March 11, 2012 at 10:49 pm
Did you create the function in one database and running the query in another database? can you double check that the function you posted has been successfully created on the database that you are running the query on?
i executed the code that Lutz sent and it worked fine for me!
March 12, 2012 at 3:11 am
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DelimitedSplit8K]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
DROP FUNCTION [dbo].[DelimitedSplit8K];
END
GO
CREATE FUNCTION [dbo].[DelimitedSplit8K]
--===== 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
DECLARE @tbl TABLE
(
id INT IDENTITY(1,1),
list VARCHAR (200)
)
INSERT INTO @tbl
SELECT '47,48,49,92,93,94,95,96,108,107' UNION ALL
SELECT '37,27';
WITH cte AS (
SELECT id, item
FROM @tbl
CROSS APPLY (SELECT *
FROM dbo.DelimitedSplit8K (list,','))x)
SELECT id,
STUFF((SELECT ', ' + item
FROM cte t2
WHERE t2.id = t1.id
ORDER BY CAST(item AS INT)
FOR XML PATH('')),1,2,'') AS new_list
FROM cte t1
GROUP BY id;
Returns: -
id new_list
----------- ------------------------------------------
1 47, 48, 49, 92, 93, 94, 95, 96, 107, 108
2 27, 37
Lutz' code works fine here.
March 12, 2012 at 3:32 am
Thanks all. It is working...
March 12, 2012 at 8:24 am
--input string
declare @s-2 as varchar(max)
set @s-2 = '47,48,49,92,93,94,95,96,108,107,37,27'
--input string delimiter
declare @delim as char(1)
set @delim = ','
--variable for output string
declare @o as varchar(max)
set @o = ''
--use tally (numbers) table to parse input string
--and coalesce to squash resulting table
select
@o = @o + coalesce([Values] + ',',',')
from
(
select top 1000000
rank() over (order by n) as ranking,
substring(@delim + @s-2 + @delim, N+1,charindex(@delim,@delim + @s-2 + @delim,N+1)-N-1) as [Values]
from
dbo.Tally
where
N < len(@delim + @s-2 + @delim)and
substring(@delim + @s-2 + @delim,N,1) = @delim
--change order by clause to asc or desc as required
order by
cast(substring(@delim + @s-2 + @delim, N+1,charindex(@delim,@delim + @s-2 + @delim,N+1)-N-1) as int)
) a
--strip trailing delimiter
select left(@o,len(@o)-1)
March 12, 2012 at 10:49 pm
Joe,
That's the old splitter style and it can get really slow. Please see the following article for the improvement of removing all concatenation.
http://www.sqlservercentral.com/articles/Tally+Table/72993/
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply