Concatenating numbers from a single numeric column in SQL without using concat function

Question

I have the following code for a single column table that has values from 0 to 9:

create table number(
    value int
)

truncate table number

insert into number values(0);
insert into number values(1);
insert into number values(2);
insert into number values(3);
insert into number values(4);
insert into number values(5);
insert into number values(6);
insert into number values(7);
insert into number values(8);
insert into number values(9);

What I want is a query which should join the numbers without using concat (or any builtin function) function and gives me the following output in a tabular form:

Really having a hard time doing it for the past 5 days. Need sincere help.

We can help you a lot more effectively if you post what you tried. Otherwise it just looks like you want someone to write code for you, do your homework, pass the interview test, ... . — HABO
– HABO, Commented Dec 27, 2015 at 15:15
It looks like you are just trying to generate the numbers from 0-999. Is there a reason you are not just using a larger numbers table? — Mitch
– Mitch, Commented Dec 27, 2015 at 18:16

Lukasz Szozda · Accepted Answer · 2015-12-27 11:58:59Z

4

You could use CROSS JOIN and simple Math formula:

SELECT [num] = n1.value * 100 + n2.value * 10 + n3.value 
FROM number n1, number n2, number n3
ORDER BY num

LiveDemo

Output:

╔═════╗
║ num ║
╠═════╣
║ 0   ║
║ 1   ║
║ 2   ║
║ 3   ║
║ 4   ║
║ 5   ║
║ 6   ║
║ 7   ║
║ ... ║
║ 999 ║
╚═════╝

EDIT:

Cross join will return records(tuples) (0,0,0), (0,0,1),(0,0,2), ...,(9,9,9) then you need to apply Positional Number System formula:

example:

^{Images from https://en.wikipedia.org/wiki/Positional_notation}

╔════════════╦═══════════╦══════════╦════════════════════╗
║ 10^2 = 100 ║ 10^1 = 10 ║ 10^0 = 1 ║        num         ║
╠════════════╬═══════════╬══════════╬════════════════════╣
║ 0 * 100    ║ 0 * 10    ║ 0 * 1    ║ 0 + 0 + 0 = 0      ║
║ 0 * 100    ║ 0 * 10    ║ 1 * 1    ║ 0 + 0 + 1 = 1      ║
║ 0 * 100    ║ 0 * 10    ║ 2 * 1    ║ 0 + 0 + 2 = 2      ║
║ ...        ║ ...       ║ ...      ║ ...                ║
║ 9 * 100    ║ 9 * 10    ║ 9 * 1    ║ 900 + 90 + 9 = 999 ║
╚════════════╩═══════════╩══════════╩════════════════════╝

edited Dec 27, 2015 at 11:58

answered Dec 27, 2015 at 11:31

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Najam Ul Hassan Over a year ago

thanks alot brother. it really works but it would be a great favour if you explain the mathematics behind it.

KumarHarsh · Accepted Answer · 2015-12-28 05:05:35Z

0

While @lad2025 query is good based on certain formula.It uses 3 cross join.

Simpler way (not sure,if this is best)

Declare  @number table(
    value int
)

insert into @number values(0);
insert into @number values(1);
insert into @number values(2);
insert into @number values(3);
insert into @number values(4);
insert into @number values(5);
insert into @number values(6);
insert into @number values(7);
insert into @number values(8);
insert into @number values(9);

Declare @MaxLimit int=999
;

WITH CTE
AS (
    SELECT value
    FROM @number A

    UNION ALL

    SELECT a.value + 10
    FROM CTE A
    WHERE A.value < @MaxLimit-9
    )
SELECT value
FROM cte
--WHERE value <= 999
ORDER BY value

answered Dec 28, 2015 at 5:05

KumarHarsh

5,0941 gold badge22 silver badges25 bronze badges

Collectives™ on Stack Overflow

Concatenating numbers from a single numeric column in SQL without using concat function

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related