## Practical Recursion – Part 3

Often complex problems can be solved with Recursive CTEs, but rarely are they used. Recursion can give even the most seasoned of developers a headache, so in this series of posts I’ll share some practical recursive CTEs I’ve used frequently for a variety of problems. Here in part 3 of the series is an example of how recursion can be used to perform splitting of a delimited list.

Also See…

Practical Recursion – Part 1

Practical Recursion – Part 2

Recursive CTE vs While Loop – Performance Analysis

SET NOCOUNT ON;

DECLARE

@ParameterValues VARCHAR(4000) = ‘Value1,Value2,Value3,Value4,Value5,Value6,Value7,Value8,Value9’

, @Delimiter CHAR(1) = ‘,’;

WITH

[ParameterValues] AS

(

SELECT

CASE

WHEN CHARINDEX(@Delimiter, @ParameterValues) = 0

THEN @ParameterValues

ELSE SUBSTRING(@ParameterValues, 1, CHARINDEX(@Delimiter, @ParameterValues) 1)

END AS [Value]

, CASE

WHEN CHARINDEX(@Delimiter, @ParameterValues) = 0

THEN NULL

ELSE RIGHT(@ParameterValues, LEN(@ParameterValues) CHARINDEX(@Delimiter, @ParameterValues))

END AS [RemainingValues]

UNION ALL

SELECT

CASE

WHEN CHARINDEX(@Delimiter, PV.[RemainingValues]) = 0

THEN PV.[RemainingValues]

ELSE SUBSTRING(PV.[RemainingValues], 1, CHARINDEX(@Delimiter, PV.[RemainingValues]) 1)

END AS [Value]

, CASE

WHEN CHARINDEX(@Delimiter, PV.[RemainingValues]) = 0

THEN NULL

ELSE RIGHT(PV.[RemainingValues], LEN(PV.[RemainingValues]) CHARINDEX(@Delimiter, PV.[RemainingValues]))

END AS [RemainingValues]

FROM

[ParameterValues] AS PV

WHERE

CHARINDEX(@Delimiter, PV.[RemainingValues]) IS NOT NULL

)

SELECT

PV.[Value]

FROM

[ParameterValues] AS PV

OPTION

(MAXRECURSION 0);

Advertisements
1. Leave a comment