Give an example of Recursive CTE

Question

Thanks in advance

Answer 1

Use:

WITH number_list AS (
  SELECT 100 'base',
         1 'digit_1',
         0 'digit_2',
         0 'digit_3'
  UNION ALL
  SELECT nl.base + 1,
         CAST(SUBSTRING(CAST(nl.base + 1 AS VARCHAR(3)), 1, 1) AS INT),
         CAST(SUBSTRING(CAST(nl.base + 1 AS VARCHAR(3)), 2, 1) AS INT),
         CAST(SUBSTRING(CAST(nl.base + 1 AS VARCHAR(3)), 3, 1) AS INT)
    FROM NUMBER_LIST nl
   WHERE nl.base + 1 <= 999)
SELECT t.base
  FROM NUMBER_LIST t
 WHERE POWER(t.digit_1, 3) + POWER(t.digit_2, 3) + POWER(t.digit_3, 3) = t.base

The first step is to generate a list of numbers, incrementing by one. Because an Armstrong number must have three digits, there isn't much point in starting at one.

The next step is to pull out the values at each digit so you can use the value in order to evaluate if the number is or is not an Armstrong number. Armstrong numbers are three digits long, and the sum of the cubes of each digit equals the number.

References:

• SUBSTRING
• CAST/CONVERT
• POWER

Addendum

---

SQL Server support for the WITH clause started with 2005. For versions prior to that, you'd get the best performance from creating a numbers table. A numbers table is a single column table, with an incrementing integer value starting at one. Realistically, use a loop to generate enough numbers.

SELECT t.base
  FROM (SELECT nt.base, 
               CAST(SUBSTRING(CAST(nt.base AS VARCHAR(3)), 1, 1) AS INT),
               CAST(SUBSTRING(CAST(nt.base AS VARCHAR(3)), 2, 1) AS INT),
               CAST(SUBSTRING(CAST(nt.base AS VARCHAR(3)), 3, 1) AS INT)
          FROM NUMBERS_TABLE nt
         WHERE nt.base BETWEEN 100 and 999) t
 WHERE POWER(t.digit_1, 3) + POWER(t.digit_2, 3) + POWER(t.digit_3, 3) = t.base

Answer 2

Also see similar question/answers here, here and here.