Numbers to Words

This advanced SQL exercise is part of the book: Advanced SQL – Practical Techniques and Use-Cases, which includes more than 100 day-to-day SQL challenges (and solutions), need-to-know advanced features, and key concepts.

Here’s a challenging question, sent by Elad Peleg.
The following table contains a list of numbers :

CREATE TABLE numbers
(
 id int
)
GO

Use a SQL query to convert each digit to a word

Quite simple, right 😉 ? Your solution should be applicable to any given number of rows and digits, and again – the real challenge is to solve it by using SQL only. (No variables, Procedures etc.)

Want to take the challenge to the next level ? How you would you SUM the digits of each number (using different method than what already posted) ? For example :

Use this script to create and populate the table


DROP TABLE numbers 

CREATE TABLE numbers
(
 id int
)
GO

INSERT INTO numbers
VALUES(12)
INSERT INTO numbers
VALUES(3456)
INSERT INTO numbers
VALUES(298)
INSERT INTO numbers
VALUES(95276)
INSERT INTO numbers
VALUES(106342)
GO

SELECT * FROM numbers

Display the solution


--------------------------------------------------------------
-- Q1 Answer by Meir Rudnitzky
--------------------------------------------------------------

if OBJECT_ID('tempdb.dbo.#numbers') is NOT NULL
    drop table #numbers
 
create table #numbers (
number int)
 
insert #numbers values
(427321),
(34),
(821035),
(6211),
(195),
(518903),
(4972)
 

select  Numbers = reverse(substring(reverse(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(cast(number as varchar),
            '0', 'zero,'),
            '1', 'one,'),
            '2', 'two,'),
            '3', 'three,'),
            '4', 'four,'),
            '5', 'five,'),
            '6', 'six,'),
            '7', 'seven,'),
            '8', 'eight,'),
            '9', 'nine,')), 2, 100))
from  #numbers

-- Or 

select  Numbers = substring(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(cast(number as varchar),
            '0', 'zero,'),
            '1', 'one,'),
            '2', 'two,'),
            '3', 'three,'),
            '4', 'four,'),
            '5', 'five,'),
            '6', 'six,'),
            '7', 'seven,'),
            '8', 'eight,'),
            '9', 'nine,'), 1, len(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(cast(number as varchar),
                                '0', 'zero,'),
                                '1', 'one,'),
                                '2', 'two,'),
                                '3', 'three,'),
                                '4', 'four,'),
                                '5', 'five,'),
                                '6', 'six,'),
                                '7', 'seven,'),
                                '8', 'eight,'),
                                '9', 'nine,')) - 1)
from  #numbers


--------------------------------------------------------------
-- Q2 Answer by Uri Dimant
--------------------------------------------------------------

create table #t (str1 varchar(50))
insert into #t SELECT * FROM numbers
 
SELECT rn, str1,SUM(CAST(items AS INT))  sum_digits 
FROM   ( SELECT *,ROW_NUMBER() OVER (ORDER BY (SELECT 0)) rn FROM #t ) AS t
CROSS APPLY (  SELECT  *   FROM   (
                                    VALUES (SUBSTRING(t.str1, 1, 1)),
                                           (SUBSTRING(t.str1, 2, 1)),
                                           (SUBSTRING(t.str1, 3, 1)),
                                           (SUBSTRING(t.str1, 4, 1)),
                                           (SUBSTRING(t.str1, 5, 1)),
                                           (SUBSTRING(t.str1, 6, 1)),
                                           (SUBSTRING(t.str1, 7, 1))
                                     ) AS f(a)
                                  --WHERE a >''
                         ) AS q(Items)
GROUP BY  rn, str1

--------------------------------------------------------------
-- Q1 Answer by Hannan Kravitz
--------------------------------------------------------------

;WITH Part_A as
(
SELECT Id, CONVERT(VARCHAR(10),Id) Id_Str 
FROM NUMS
) 
, Part_B AS
(
SELECT Id, List = 
    REPLACE(
        REPLACE(
            REPLACE(
                REPLACE(
                    REPLACE(
                        REPLACE(
                            REPLACE(
                                REPLACE(
                                    REPLACE(
                                        REPLACE(
                                    Id_Str,1,'One,'),
                                2,'Two,'),
                            3,'Three,'),
                        4,'Four,'),
                    5, 'Five,'),
                6,'Six,'),
            7,'Seven,'),
        8,'Eight,'),
    9,'Nine,'),
0,'Zero,')
FROM Part_A
)
SELECT Id,  CASE WHEN RIGHT(List,1) = ',' THEN SUBSTRING(List,1,len(List)-1)
            WHEN LEFT(List,1) = ',' THEN STUFF(List,1,1,'') 
            ELSE List 
            END
FROM Part_B

--------------------------------------------------------------
-- Q1 Answer by Manish Ramrakhiani
--------------------------------------------------------------

with cte
as (select id, n = 1
from numbers
union all
select id, n = n + 1
from cte
where n < len(id))
select distinct a.id,
replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(
stuff((select ',' + substring(CAST(b.id as nvarchar),b.n,1)
from cte AS b
where b.id = a.id
for XML PATH('')),1,1,'')
,'0','zero')
,'1','one')
,'2','two')
,'3','three')
,'4','four')
,'5','five')
,'6','six')
,'7','seven')
,'8','eight')
,'9','nine')
from cte as a

--------------------------------------------------------------
-- Q1 Answer by Hannan Kravitz
--------------------------------------------------------------

;WITH digits (Num,Num_Desc) AS /*Define the translate table*/
(
SELECT 0,'Zero'
UNION ALL
SELECT 1,'One'
UNION ALL
SELECT 2,'Two'
UNION ALL
SELECT 3,'Three'
UNION ALL
SELECT 4,'Four'
UNION ALL
SELECT 5,'Five'
UNION ALL
SELECT 6,'Six'
UNION ALL
SELECT 7,'Seven'
UNION ALL
SELECT 8,'Eight'
UNION ALL
SELECT 9,'Nine'
 
)
 
, Work_Table as
(
SELECT ROW_NUMBER () OVER (ORDER BY (SELECT NULL)) Seq,
Id,
CONVERT(VARCHAR(10),Id) Id_Str,
LEN(id) Num_Size
FROM numbers
)
 
,Rec_CTE as
(
select Seq, Id ,Id_Str , 1 Num , SUBSTRING(Id_Str, 1, 1) Item
from Work_Table
union all
select Seq, Id ,Id_Str, Num + 1 , SUBSTRING(Id_Str , Num +1 , 1)
from Rec_CTE
where Num < len(Id_str)
)
 
select Id , STUFF((SELECT ','+Num_Desc FROM Rec_CTE b
join digits d
on b.Item = d.Num WHERE b.Seq = r.Seq FOR XML PATH (''),TYPE ).value('.','varchar(400)'),1,1,'') List
from Rec_CTE r
group by Id , Seq

--------------------------------------------------------------
-- Q1 Answer by Hannan Kravitz
--------------------------------------------------------------

CREATE FUNCTION [dbo].[udf_Row_To_Col] 
    (
    @Str VARCHAR(300)
    )
RETURNS TABLE AS RETURN
WITH Nums (n) as
(
        SELECT TOP (Len(@Str)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
        FROM (VALUES (0),(0),(0),(0),(0)) a(n)
        CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0)) b(n)
        CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) c(n)
         
)
SELECT Items = SUBSTRING(@Str, n,1) 
FROM Nums
GO


;WITH digits (Num,Num_Desc) AS /*Define the translate table*/
(
SELECT 0,'Zero'
UNION ALL
SELECT 1,'One'
UNION ALL
SELECT 2,'Two'
UNION ALL
SELECT 3,'Three'
UNION ALL
SELECT 4,'Four'
UNION ALL
SELECT 5,'Five'
UNION ALL
SELECT 6,'Six'
UNION ALL
SELECT 7,'Seven'
UNION ALL
SELECT 8,'Eight'
UNION ALL
SELECT 9,'Nine'
 
)
 
,Work_Table as /*Creating a sequence that will be used to maintain the order of the numbers*/
(
SELECT ROW_NUMBER () OVER (ORDER BY (SELECT NULL)) Seq,  CONVERT(VARCHAR(10),Id) Id_Str , Id
FROM nums
)
 
,Main as /*Apply the function foreach record in the table*/
(
SELECT Seq ,wt.Id, d.Num_Desc
FROM Work_Table Wt
    CROSS APPLY dbo.udf_Row_To_Col(CONVERT(VARCHAR(10),Id_Str) ) i
JOIN digits d
ON i.Items = d.Num
)
 
/*Concatenating the translate table using the for xml path clause*/
SELECT  Id, STUFF((SELECT ','+Num_Desc FROM Main b WHERE b.Seq = a.Seq FOR XML PATH (''),TYPE ).value('.','varchar(400)'),1,1,'')
FROM Main a
GROUP BY Seq , Id

--------------------------------------------------------------
-- Q1 Answer by Jason Rogers
--------------------------------------------------------------

DECLARE @myTable TABLE (myID INT);
 
 
INSERT INTO @myTable
VALUES (12), (3456), (298), (95276), (106342);
 
WITH l0 (n) AS
  (SELECT 1
   UNION ALL SELECT 1),
     l1 (n) AS
  (SELECT 1
   FROM l0 AS a
   CROSS JOIN l0 AS b),
     l2 (n) AS
  (SELECT 1
   FROM l1 AS a
   CROSS JOIN l1 AS b),
     ctePosition (n) AS
  (SELECT ROW_NUMBER() OVER (
                             ORDER BY
                               (SELECT NULL))
   FROM l2),
     cteTable1 (myID, myIDLength) AS
  (SELECT CAST(myID AS VARCHAR(10)),
          LEN(CAST(myID AS VARCHAR(10)))
   FROM @myTable),
     cteTable2 (myID, n, myWord) AS
  (SELECT t.myID,
          p.n,
          p.myWord
   FROM cteTable1 AS t CROSS APPLY
     (SELECT TOP (t.myIDLength) n, myWord = CASE SUBSTRING(t.myID, n, 1)
                                                WHEN '0' THEN 'zero'
                                                WHEN '1' THEN 'one'
                                                WHEN '2' THEN 'two'
                                                WHEN '3' THEN 'three'
                                                WHEN '4' THEN 'four'
                                                WHEN '5' THEN 'five'
                                                WHEN '6' THEN 'six'
                                                WHEN '7' THEN 'seven'
                                                WHEN '8' THEN 'eight'
                                                ELSE 'nine'
                                            END
      FROM ctePosition) AS p)
SELECT DISTINCT a.myID,
                c.myList
FROM cteTable2 AS a CROSS APPLY
  (SELECT myList = STUFF(
                           (SELECT ',' + b.myWord
                            FROM cteTable2 AS b
                            WHERE (b.myID = a.myID)
                            ORDER BY b.n ASC
                            FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 1, '')) AS c

--------------------------------------------------------------
-- Q2 Answer by Hannan Kravitz
--------------------------------------------------------------

WITH Nums (n)
AS
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) a(n) /*Assuming that there won't be a number greater than 15 digits int the table….*/
)
SELECT Id ,
SUM(CAST(SUBSTRING(cast(Id AS VARCHAR(10)), n , 1) AS INT)) Sum_Digits
FROM Nums
CROSS JOIN Numbers
WHERE N <= (SELECT MAX(LEN(Id)) FROM Numbers) /*Limits the loop*/
GROUP BY Id

UpScale Analytics is one of the largest platforms in the world for learning SQL by doing, consisting over 300 SQL exercises at different levels (including solutions), by topics, across over 100 different datasets. More…

Numbers to Words

About Us

Popular Courses

Desicion Making with Basic SQL

PL/SQL Programming

Advanced Power BI

Quick Links