Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

Simulating cross apply with CSV-records in SQL Server 2000

DECLARE @Sample TABLE (Col1 VARCHAR(6), Col3 VARCHAR(200))

INSERT @Sample
SELECT '123', '125,124,126' UNION ALL
SELECT '124', '127,21,245'

--SELECT         Col1,
--                Data
--FROM            @Sample
--CROSS APPLY     fnParseList(',', Col3)

SELECT          a.Col1,
                SUBSTRING(',' + a.Col3 + ',', n.Number + 1, CHARINDEX(',', ',' + a.Col3 + ',', n.Number + 1) - n.Number - 1) AS [Value]
FROM            @Sample AS a
INNER JOIN      master..spt_values AS n ON SUBSTRING(',' + a.Col3 + ',', n.Number, 1) = ','
WHERE           n.Type = 'p'
                AND n.Number >
                AND n.Number < LEN(',' + a.Col3 + ',')
kick it on DotNetKicks.com

Print | posted on Monday, October 15, 2007 8:25 AM | Filed Under [ Algorithms SQL Server 2000 ]

Feedback

Gravatar

# re: Cross apply in SQL Server 2000

couldn't resist huh :))
kick it!!
10/15/2007 11:56 AM | Mladen
Gravatar

# re: Cross apply in SQL Server 2000

I don't get it. That's just a JOIN. You should title the post "parsing CSV's set-based" or something like that, but it has nothing to do with CROSS APPLY.
10/17/2007 10:25 PM | Jeff
Gravatar

# re: Simulating cross apply with CSV-records in SQL Server 2000

Noted. And title is changed.
10/18/2007 7:49 AM | Peter Larsson
Gravatar

# re: Simulating cross apply with CSV-records in SQL Server 2000

Do you also mean that your approach is alternate of this?


select
t1.col1,t2.number
from
@sample t1
cross join (
select number from master..spt_values
where Type = 'p'
AND Number > 0
) t2
where
','+cast(t1.col3 as varchar(2000))+',' like '%,'+cast(t2.number as varchar(10))+',%'
10/18/2007 3:01 PM | Madhivanan
Gravatar

# re: Simulating cross apply with CSV-records in SQL Server 2000

Well. Ignore my comment. It wont work for all set of data
10/18/2007 3:10 PM | Madhivanan
Gravatar

# re: Simulating cross apply with CSV-records in SQL Server 2000

useless
1/27/2009 6:12 AM | sdfs
Gravatar

# re: Simulating cross apply with CSV-records in SQL Server 2000

Awesome..Brilliant
11/25/2009 2:50 PM | xman
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET