15,606,568 members
Articles / Database Development / SQL Server
Article
Posted 9 Jan 2010

19.2K views
6 bookmarked

# Armstrong Number Generation Within a Range (In SQL Server)

Rate me:
How to generate an Armstrong number between 0 and 999.

## 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:

SQL
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:

SQL
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)

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.