Simple recursive CTE

October 20, 2006

I like CTEs. Mostly because it lets me have a derived table that I can refer to multiple times. I don't often use the recursive features of it. But when I do, I repeatedly feel amazed at the power available.

Today I had a challenge to produce a nice SQL way of converting integers to binary

So I pulled out the CTE. 🙂

with ctebins as
(select num as num_orig, num as working_level, cast('' as varchar(max)) as binval
from nums_bin
union all
select c.num_orig, c.working_level / 2, cast(c.working_level % 2 as varchar(max)) + c.binval
from ctebins c
where c.working_level > 0
select num_orig, binval
from ctebins
where working_level = 0

What this is doing is to populate the top level of the CTE with a bunch of numbers from nums_bin, along with a blank string. I know I don't have to use varchar(max) – it's not going to get that big after all…

Then it fills the CTE with rows using recursion. I keep the original number (to use later), and keep cutting it in half to get the binary number out. Then I query the full CTE for the last level of recursion – when my working number got down to zero.

It works nicely, and it's very fast on a big set of numbers. 


This Post Has 2 Comments

  1. Mitch Wheat

    Hi Rob

    I had a go at writing a procedural T-SQL version over at my blog. I wonder if it’s any faster…

  2. robfarley

    Yes, well done. See my latest post for comments….

Leave a Reply

LobsterPot Blogs

Blog posts by Rob Farley and other LobsterPot Solutions team members.


Related Blogs