Recursion is possible in SQL via the use of a type of subquery known as a Common Table Element (CTE).
One handy feature of CTEs is that they have names which makes it easy for us to reference these subqueries when we want.
Let’s name our cte
Now, we can invoke
cteRecursion from within
That is, we write a CTE that applies some transformation to some input recursively until some stopping condition is met.
WITH cteRecursion AS ( SELECT anchorMembers UNION ALL SELECT recursiveMembers FROM cteRecursion ) SELECT * FROM cteRecursion;
As you can see, the CTE contains a union between so-called anchor members and recursive members.
One general area where I find recursion to be particularly useful in SQL is regex. For example, suppose we have a string of comma-separated values:
DECLARE @str = 'Eggs,Milk,Juice,Bread,';
Now, let’s write a SQL query that can separate this string to a column of values.
First we write the transformation for our anchor members
These anchor transformations define what happens in our recursion on the first iteration.
SELECT SUBSTRING(@str,0,CHARINDEX(',',@str,1)) AS output , SUBSTRING(@str,CHARINDEX(',',@str,1) + 1, LEN(@str)) AS remainder
We’ve split our string into two parts:
Now we recurse through the latter on subsequent iterations.
The transformations for the recursive members are identical to the transformations for the anchor members.
But now we invoke
cteRecursion and include a stopping condition, which stops the recursion when
remainder contains no more commas.
WITH cteRecursion AS ( SELECT -- anchor members SUBSTRING(@str,0,CHARINDEX(',',@str,1)) AS output , SUBSTRING(@str,CHARINDEX(',',@str,1) + 1, LEN(@str)) as remainder UNION ALL SELECT -- recursive members SUBSTRING(remainder,0,CHARINDEX(',',remainder,1)) , SUBSTRING(remainder,CHARINDEX(',',remainder,1) + 1, LEN(remainder)) FROM cteRecursion -- invoke CTE WHERE CHARINDEX(',', remainder,1) <> 0 -- stopping condition ) SELECT output FROM cteRecursion;
So after some number of iterations, we return the following: