Hello,
You can also use CTEs under SQL Server 2005 to do the same thing.
The advantage is that you can specify a JOIN on a CTE :
DECLARE @str VARCHAR(64)
SET @str = 'toto,titi,tutu,tata';
WITH CTE (Deb, Fin) AS
(
SELECT 1 Deb, CHARINDEX(',', @str + ',') Fin
UNION ALL
SELECT Fin + 1, CHARINDEX(',', @str + ',', Fin + 1)
FROM CTE
WHERE CHARINDEX( ',', @str + ',', Fin + 1 ) > 0
)
SELECT SUBSTRING(@str, Deb , Fin - Deb), Deb, Fin
FROM CTE
-------------------------------------------
DECLARE @String VARCHAR(64) SET @String = 'un,deux,trois,quatre,cinq';
DECLARE @Separator CHAR(1) SET @Separator = ',';
WITH INDICES AS
(
SELECT 0 Deb, 1 Fin
UNION ALL
SELECT Fin, CHARINDEX(@Separator, @String, Fin) + LEN(@Separator)
FROM INDICES
WHERE Fin > Deb
)
SELECT SUBSTRING(
@String,
Deb,
CASE
WHEN Fin > LEN(@Separator) THEN Fin - Deb - LEN(@Separator)
ELSE LEN(@String) - Deb + 1
END
) String,
Deb,
Fin,
LEN(@String) - Deb + 1 Lgr
FROM INDICES WHERE Deb > 0
