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