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

INSERT	@Sample
SELECT	'123', '125,124,126' UNION ALL
SELECT	'124', '127,21,245' UNION ALL
-- Edge condition data:
SELECT	'125', '' UNION ALL
SELECT	'126', ',1' UNION ALL
SELECT	'127', '2,' UNION ALL
SELECT	'128', ',3,'UNION ALL
SELECT	'129', NULL		-- Note: NULL is Ignored

-- SQL 2000 Version
SELECT	S.Col1,
	[Value] = SUBSTRING(',' + S.Col3 + ',', N.mb_int_ID + 1, 
		CHARINDEX(',', ',' + S.Col3 + ',',
			N.mb_int_ID + 1) - N.mb_int_ID - 1)
FROM	@Sample AS S
	-- Use a Tally table if you have one!!!
--	JOIN dbo.kk_MB_INT_Integer AS N
	JOIN
	(
		SELECT v0.n + v1.n + v2.n + v3.n AS mb_int_ID
		FROM
		(
			SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 
			UNION ALL SELECT  4 UNION ALL SELECT  5 UNION ALL SELECT  6 UNION ALL SELECT  7
			UNION ALL SELECT  8 UNION ALL SELECT  9 UNION ALL SELECT 10 UNION ALL SELECT 11
			UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15
		)v0,
		(
			SELECT 0 n UNION ALL SELECT 16 UNION ALL SELECT 32 UNION ALL SELECT 48 
			UNION ALL SELECT  64 UNION ALL SELECT  80 UNION ALL SELECT  96 UNION ALL SELECT 112
			UNION ALL SELECT 128 UNION ALL SELECT 144 UNION ALL SELECT 160 UNION ALL SELECT 176
			UNION ALL SELECT 192 UNION ALL SELECT 208 UNION ALL SELECT 224 UNION ALL SELECT 240
		) v1,
		(
			SELECT 0 n UNION ALL SELECT 256 UNION ALL SELECT 512 UNION ALL SELECT 768
			UNION ALL SELECT 1024 UNION ALL SELECT 1280 UNION ALL SELECT 1536 UNION ALL SELECT 1792
			UNION ALL SELECT 2048 UNION ALL SELECT 2304 UNION ALL SELECT 2560 UNION ALL SELECT 2816
			UNION ALL SELECT 3072 UNION ALL SELECT 3328 UNION ALL SELECT 3584 UNION ALL SELECT 3840
		) v2,
		(
			SELECT 0 n UNION ALL SELECT 4096
		) v3
	) AS N
		 ON N.mb_int_ID < LEN(',' + S.Col3 + ',')
		AND SUBSTRING(',' + S.Col3 + ',', N.mb_int_ID, 1) = ',' 

-- SQL 2005 version
SELECT	S.Col1,
	V.Value
FROM	@Sample AS S
	CROSS APPLY dbo.MySplitFunction(Col3, ',', NULL) AS V

quote:

---

Is recursion still limited to only 32 levels in SQL Server 2005? If it is, BOOOOOM on the 33 parameter that needs to be split... (I could be wrong... might be different on CTE's which I've not had the pleasure to work with, yet.)

---

quote:

---

Para evitar que se genere un bucle infinito, se puede limitar el número de niveles de recursividad permitidos para una instrucción determinada mediante el uso de la sugerencia MAXRECURSION y un valor de 0 a 32.767 en la cláusula OPTION de la instrucción INSERT, UPDATE, DELETE o SELECT. De esta manera, se puede controlar la ejecución de la instrucción hasta que se resuelva el problema de código que genera el bucle. El valor predeterminado para todo el servidor es 100. Si se especifica 0, no se aplica ningún límite. Sólo se puede especificar un valor de MAXRECURSION por instrucción

---

USE [MiBD]
GO
IF EXISTS (
    SELECT 1
    FROM INFORMATION_SCHEMA.ROUTINES
    WHERE ROUTINE_NAME = 'Split')
BEGIN
    DROP FUNCTION Split
END
GO
CREATE FUNCTION Split
(
  @Id        varchar(8000),
  @Separador varchar(8)   = ','
)
RETURNS TABLE
--WITH ENCRYPTION
AS
RETURN
(
  WITH SplitETC (Inicio, Fin)
    AS
  (
       SELECT 1,
              CHARINDEX(@Separador, @Id)
    UNION ALL
       SELECT Fin + LEN(@Separador),
              CHARINDEX(@Separador, @Id, Fin + LEN(@Separador))
         FROM SplitETC
        WHERE Fin > 0
  )
  SELECT RTRIM(LTRIM(SUBSTRING(@Id, Inicio, Largo))) Id
    FROM (SELECT Inicio,
                 CASE WHEN Fin IS NULL OR Fin = 0 THEN
                     LEN(@Id) + 1
                 ELSE
                     Fin
                 END  - Inicio  AS Largo
            FROM SplitETC OPTION (MAXRECURSION 0)) AS Split
)
GO

-- Create a comma delimited string for testing
declare @str varchar(max)
select @str = '02315A, 02311B, 02312A, 00889CS, 00093A, 00854DG, 00854DJ'

-- XML tag the string by replacing commas with </v><v> tags
declare @xml xml
select @xml = cast('<v>'+ replace(@str,',','</v><v>') + '</v>' as xml)

-- Finally select values from nodes <v> and trim at the same time
select ltrim(rtrim(mynode.value('.[1]', 'nvarchar(12)'))) as Code
from (select @xml doc) xx
cross apply doc.nodes('/v') (mynode)

quote:

---

Originally posted by tofo

What about using XML parsing to split like this?

---

select @xml = cast('<v><![CDATA['+ replace(@str,',',']]></v><v><![CDATA[') + ']]></v>' as xml)