Armstrong Number Generation Within a Range (In SQL Server)

Introduction

Though the title of the article says Armstrong Number Generation, basically, the idea is to present the concept of a combination of number tables with Recursive CTE.

This article is meant for those who are not aware of what a number table is, what a CTE is, and how efficiently we can write complex programs using a combination of those.

Background

What is an Armstrong number?

If the sum of the cubes of individual digits of a number is equal to that number, it is an Armstrong number.

Using the code

A number table is a table that contains only sequential numbers. It helps us in many situations, especially which dealing with string operations. There are many ways of generating them, although I will only be focusing on generating them using the Recursive CTE approach.

For doing this, we can do:

WITH NumTab AS(
SELECT 1 AS Num UNION ALL SELECT Num+1 FROM NumTab WHERE Num <= 100)
SELECT * FROM NumTab

The above program will generate a number between 1 and 100.

Among the various alternatives to generate an Armstrong number, here is one way of doing it:

WITH NumTab AS(
SELECT 0 AS Num UNION ALL SELECT Num+1 FROM NumTab WHERE Num < 999)
SELECT ArmstrongNumber = Num FROM NumTab
WHERE 
    Num = POWER(COALESCE(SUBSTRING(cast(Num AS VARCHAR(10)),1,1),0),3)
        + POWER(COALESCE(SUBSTRING(cast(Num AS VARCHAR(10)),2,1),0),3)
        + POWER(COALESCE(SUBSTRING(cast(Num AS VARCHAR(10)),3,1),0),3)    
OPTION(MAXRECURSION 0)

Output:

ArmstrongNumber
0
1
153
370
371
407

Explanation

First, I am generating a number table and then picking up the individual digits from the number. I then take the sum of the cubes of the individual numbers and compare it with the original number to see if it an Armstrong number.

Points of interest

This program takes the help of a number table that is created by using the Recursive CTE (SQL Server 2005 feature). I am open for any suggestions that will help me improve the article.