SQL Server - Return All possible combinations of a 4 digit number passed to a stored procedure

Question

I have a task to write a stored procedure or a function to return all possible combinations of a 4 digit number.

For example, if I pass 1234 to the stored procedure or function, it should return 4 digit numbers (all possible combinations), like

1123, 1112, 1324, 1342, 2134, 2234

and so on.

It can be of 4 digits only.

I have been doing this with using LIKE operator:

select * 
from Table 
where mynumber like '%1%'
  and mynumber like '%2%'
  and mynumber like '%3%'
  and mynumber like '%4%'

but the problem is, I have hardcoded the numbers 1,2,3 and 4.

The number can be anything.

And these many LIKE operators can also impact the performance on a large table.

Can anybody give me some generic query to get the combinations?

Thanks in advance.

I don't think this problem is well suited to a query. I'd look into writing a UDF. — Tim Biegeleisen, Jan 05 '18 at 06:43
given a four-digit number, do you want to just get all possible jumbled combination or can each number be used more than once? — xGeo, Jan 05 '18 at 07:17

score 4 · Answer 1 · edited Jan 05 '18 at 08:14

4

You can use a cross join:

with digits as (
      select substring(num, 1, 1) as d union all
      select substring(num, 2, 1) as d union all
      select substring(num, 3, 1) as d union all
      select substring(num, 4, 1) as d 
     )
select (d1.d + d2.d + d3.d + d4.d)
from digits d1 cross join
     digits d2 cross join
     digits d3 cross join
     digits d4;

Note: This assumes that the number is a string (based on the fact that you use like in your question).

edited Jan 05 '18 at 08:14

TheGameiswar

27,855
8
56
94

answered Jan 05 '18 at 06:48

Gordon Linoff

1,242,037
58
646
786

i think you mean `substring`, not `substr`? – xGeo Jan 05 '18 at 06:55
i think you also have incorrect syntax: `select substr(num, 4, 1) as d union all` - remove `union all`? – xGeo Jan 05 '18 at 07:02

John Wu · Answer 2 · 2018-01-05T07:38:13.070

First you need to be able to break a four-digit number into separate digits. I suggest using a table variable and the modulus operator. Assuming we have an integer input named @input, we can break it into its digits using this:

    DECLARE @Digits Table(Number int)

    INSERT INTO @Digits(Number)
    VALUES (@input % 10),
           (@input / 10 % 10),
           (@input / 100 % 10),
           (@input / 1000 % 10)

Now we have a table with four rows, one row per digit.

To create a combination of four digits, we need to include the table four times, meaning we need three joins. The joins have to be set up so no digit is duplicated. Thus our FROM and JOIN clauses will look like this:

    FROM @Digits D1
    JOIN @Digits D2 ON  D2.Number <> D1.Number
    JOIN @Digits D3 ON  D3.Number <> D1.Number
                    AND D3.Number <> D2.Number
    JOIN @Digits D4 ON  D4.Number <> D1.Number
                    AND D4.Number <> D2.Number
                    AND D4.Number <> D3.Number

Now to take the values and make a new, four-digit integer:

    SELECT Number = D1.Number * 1000 
                  + D2.Number * 100
                  + D3.Number * 10
                  + D4.Number

The complete solution:

CREATE PROC Combine(@input AS int)
AS
BEGIN
    DECLARE @Digits Table(Number int)
    ;
    INSERT INTO @Digits(Number)
    VALUES (@input % 10),
           (@input / 10 % 10),
           (@input / 100 % 10),
           (@input / 1000 % 10)
    ;
    SELECT Number = D1.Number * 1000 
                  + D2.Number * 100
                  + D3.Number * 10
                  + D4.Number
    FROM @Digits D1
    JOIN @Digits D2 ON  D2.Number <> D1.Number
    JOIN @Digits D3 ON  D3.Number <> D1.Number
                    AND D3.Number <> D2.Number
    JOIN @Digits D4 ON  D4.Number <> D1.Number
                    AND D4.Number <> D2.Number
                    AND D4.Number <> D3.Number
    ORDER BY Number
    ;
END

Usage:

EXEC Combine 1234

Resultset:

Click here to run the above code on RexTester

score 0 · Answer 3 · answered Jan 05 '18 at 06:45

0

You can try this.

select * from Table where mynumber like '%[1234][1234][1234][1234]%'

if it should only be 4 digit

select * from Table where mynumber like '[1234][1234][1234][1234]'

Also, you can use [1-4] instead of [1234]

answered Jan 05 '18 at 06:45

Serkan Arslan

13,158
4
29
44

score 0 · Answer 4 · answered Jan 05 '18 at 06:48

Here's query to return all combinations of four digits (characters in general):

select A.col + B.col + C.col + D.col [Combinations] from
(values ('1'),('2'),('3'),('4')) as A(col) cross join
(values ('1'),('2'),('3'),('4')) as B(col) cross join
(values ('1'),('2'),('3'),('4')) as C(col) cross join
(values ('1'),('2'),('3'),('4')) as D(col)

score 0 · Answer 5 · answered Jan 05 '18 at 06:48

0

Taking inspiration from this answer:

WITH n AS (
    SELECT n FROM (VALUES (1), (2), (3), (4)) n (n)
) SELECT ones.n + 10*tens.n + 100*hundreds.n + 1000*thousands.n
    FROM n ones, n tens, n hundreds, n thousands

answered Jan 05 '18 at 06:48

Robby Cornelissen

91,784
22
134
156

score 0 · Answer 6 · edited Jan 05 '18 at 06:55

You can define a table in your stored procedure will all possible combinations but using letters for codding:

DECLARE @Combinations TABLE
(
    [value] CHAR(4)
);

INSERT INTO @Combinations ([value])
VALUES ('AAAA')
      ,('AAAB')
      ,('AAAC')
      ,('AAAD')
      ...

Then update every latter with the input number:

DECLARE @Numner1 TINYINT = 2
       ,@Numner2 TINYINT = 5
       ,@Numner3 TINYINT = 1
       ,@Numner4 TINYINT = 3;

UPDATE @Combinations
SET [value] = REPLACE([value], 'A', @Numner1);

UPDATE @Combinations
SET [value] = REPLACE([value], 'B', @Numner2);

UPDATE @Combinations
SET [value] = REPLACE([value], 'C', @Numner3);     

UPDATE @Combinations
SET [value] = REPLACE([value], 'D', @Numner4);

Then just join the table with your table:

select * 
from Table A
INNER JOIN @Combinations B
    ON A.[mynumber] = B.[value];

score 0 · Answer 7 · answered Jan 05 '18 at 07:25

Try This approach

DECLARE @Num INT = 5432

;WITH CTE
AS
(
    SELECT
        SeqNo = 1,
        Original = CAST(@Num AS VARCHAR(20)),
        Num = SUBSTRING(CAST(@Num AS VARCHAR(20)),1,1)

    UNION ALL

    SELECT
        SeqNo = SeqNo+1,
        Original,
        Num = SUBSTRING(Original,SeqNo+1,1)
        FROM CTE
            WHERE SeqNo < LEN(Original)
)
SELECT 
    MyStr = C1.Num+C2.Num+C3.Num+C4.Num
    FROM CTE C1
        CROSS JOIN CTE C2
        CROSS JOIN CTE C3
        CROSS JOIN CTE C4
    WHERE 
    (
        C1.SeqNo <> C2.SeqNo
        AND 
        C3.SeqNo <> C4.SeqNo
        AND
        C4.SeqNo <> C1.SeqNo
        AND
        C2.SeqNo <> C3.SeqNo
        AND
        C1.SeqNo <> C3.SeqNo
        AND
        C4.SeqNo <> C2.SeqNo
    )
    ORDER BY 1

My Result

score 0 · Answer 8 · answered Jan 05 '18 at 07:57

Please try this. SET BASED Approach to generate all Possible combinations of a number-

IF OBJECT_ID('Tempdb..#T') IS NOT NULL
    DROP TABLE tempdb..#T

DECLARE @ AS INT = 1234

IF LEN(@) <= 7 

BEGIN 

DECLARE @str AS VARCHAR(100) 
SET @str = CAST(@ AS VARCHAR(100)) 

DECLARE @cols AS VARCHAR(100) = '' 

       SELECT DISTINCT SUBSTRING(@str,NUMBER,1) n INTO #T FROM MASTER..spt_values WHERE number > 0 AND number <= LEN(@) 

       SELECT @cols = @cols + r 
       FROM ( SELECT DISTINCT CONCAT(', o',number,'.n') r FROM MASTER..spt_values WHERE number > 0 AND number <= (LEN(@)-1)     )q

    DECLARE @ExecStr AS VARCHAR(1000) = ''
    SET @ExecStr = 'SELECT CAST(CONCAT( a.n' + @cols  +  ' ) AS INT) Combinations FROM #T a'

    SELECT @ExecStr = @ExecStr + r FROM 
    (
                    SELECT DISTINCT CONCAT(' CROSS APPLY ( SELECT * FROM #T b' , number , ' WHERE ( b' , number, '.n' , ' <> a.n ) ', 
                    CASE WHEN number = 1 then '' 
                         WHEN number = 2 then ' AND ( b2.n <> o1.n )'   
                         WHEN number = 3 then ' AND ( b3.n <> o1.n )  AND ( b3.n <> o2.n ) '     
                         WHEN number = 4 then ' AND ( b4.n <> o1.n )  AND ( b4.n <> o2.n ) AND ( b4.n <> o3.n ) '    
                         WHEN number = 5 then ' AND ( b5.n <> o1.n )  AND ( b5.n <> o2.n ) AND ( b5.n <> o3.n ) AND ( b5.n <> o4.n ) '   
                         WHEN number = 6 then ' AND ( b6.n <> o1.n )  AND ( b6.n <> o2.n ) AND ( b6.n <> o3.n ) AND ( b6.n <> o4.n ) AND ( b6.n <> o5.n ) '  
                    END
                     ,') o' , number  ) r FROM 
                    MASTER..spt_values 
                    WHERE number > 0 AND number <= (LEN(@)-1)
    )p

    EXEC (@ExecStr)
END

IF OBJECT_ID('tempdb..#T') IS NOT NULL
    DROP TABLE tempdb..#T

OUTPUT

from - https://msbiskills.com/2016/05/20/sql-puzzle-generate-possible-combinations-of-a-number-puzzle/

score 0 · Answer 9 · answered Jan 05 '18 at 07:59

You can try following alternative SQL Script as well

declare @param varchar(4) = '1234'

;with combination as (
 select 
    distinct rn = DENSE_RANK() over (Order By num), num 
 from (
    select substring(@param,1,1) as num
    union all
    select substring(@param,2,1)
    union all
    select substring(@param,3,1)
    union all
    select substring(@param,4,1)
 ) t
)
select
 c1.num, c2.num, c3.num, c4.num,
 cast(c1.num as char(1)) + cast(c2.num as char(1)) + cast(c3.num as char(1)) + cast(c4.num as char(1)) as number
from combination c1, combination c2, combination c3, combination c4

It produces 256 numbers for 4 digits

Actually this code is from SQL code which returns non-repeatable combinations in SQL of given set of items, but modified it to enable repeats of items in the output

score 0 · Accepted Answer · answered Jan 05 '18 at 08:12

Improving @GordonLinoff's answer, you can add an additional column in you CTE so that you can only make sure that each number is only used once:

declare @num varchar(max);
set @num = '1234';

with numCTE as (
  select SUBSTRING(@num, 1,1) as col, 1 as cnt union 
  select SUBSTRING(@num, 2,1) as col, 3 as cnt union 
  select SUBSTRING(@num, 3,1) as col, 9 as cnt union 
  select SUBSTRING(@num, 4,1) as col, 27 as cnt
) 
select DISTINCT (a1.col+a2.col+a3.col+a4.col)
from numCTE a1
cross join numCTE a2
cross join numCTE a3
cross join numCTE a4
where a1.cnt + a2.cnt + a3.cnt + a4.cnt = 40

Additionally, you can remove the WHERE to allow each number to be used more than once.

Don't forget the DISTINCT keyword. :)

SQL Server - Return All possible combinations of a 4 digit number passed to a stored procedure

10 Answers10