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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: