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 `cteRecursion`

.

Now, we can invoke `cteRecursion`

from *within* `cteRecursion`

.
That is, we write a CTE that applies some transformation to some input recursively until some **stopping condition** is met.

## Pseudocode

```
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**.

## Handling Regex

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,';
```

## Anchor

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 `output`

and `remainder`

.
These anchor transformations define what happens in our recursion on the first iteration.

### SQL

```
SELECT
SUBSTRING(@str,0,CHARINDEX(',',@str,1)) AS output
, SUBSTRING(@str,CHARINDEX(',',@str,1) + 1, LEN(@str)) AS remainder
```

### Output

\[\begin{array}{|c|c|} \hline \text{output} & \text{remainder} \\ \hline \text{eggs} & \text{Milk,Juice,Bread} \\ \hline \end{array}\]We’ve split our string into two parts: `output`

and `remainder`

.
Now we recurse through the latter on subsequent iterations.

## Recursion

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.

### SQL

```
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;
```

### Final Output

So after some number of iterations, we return the following:

\[\begin{array}{|c|} \hline \text{output} \\ \hline \text{Eggs} \\ \text{Milk} \\ \text{Juice} \\ \text{Bread} \\ \hline \end{array}\]