# Thinking outside the box

Patron Saint of Lost Yaks

## 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 + ',')

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

## #re: Cross apply in SQL Server 2000

couldn't resist huh :))
kick it!!

## #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

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

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

## #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))+',%'

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

Well. Ignore my comment. It wont work for all set of data