Friday, October 1, 2010

Generate Number Combination

Prompted by a friend on an interesting thread today in SQLTeam. OP ask for help to generate a number combination result with input like ‘X123’, the result expected will be 10 records

0123
1123
2123
3123
4123
5123
6123
7123
8123
9123



Basically the idea is to replace X with number 0 to 9. This can be easily done by cross join it to a number table and replace the X with the number.


The twist is for input like ‘X1X3’, the result is not just 10 records by 100 records. Like 0103, 1103, 2103, . . . 0113, 0123, 0133 etc


To achieve this, what came to my mind is to use derived table or CTE and cross join to a numbers table (tally table) 2 times. (see Number Table on how to create one)


And this is what i came up with



declare    @input_num char(4)
 
select    @input_num = '1X3X'
 
; with 
cte as
(
    select    input_num = @input_num
),
cte1 as
(
    select    input_num = isnull(n.input_num, c.input_num)
    from    cte c
        outer apply
        (
            select    input_num = stuff(input_num, 1, 1, n.num)
            from    numbers n    
            where    n.num between 0 and 9
            and    substring(input_num, 1, 1) = 'X'
        ) n
),
cte2 as
(
    select    input_num = isnull(n.input_num, c.input_num)
    from    cte1 c
        outer apply
        (
            select    input_num = stuff(input_num, 2, 1, n.num)
            from    numbers n    
            where    n.num between 0 and 9
            and    substring(input_num, 2, 1) = 'X'
        ) n
),
cte3 as
(
    select    input_num = isnull(n.input_num, c.input_num)
    from    cte2 c
        outer apply
        (
            select    input_num = stuff(input_num, 3, 1, n.num)
            from    numbers n    
            where    n.num between 0 and 9
            and    substring(input_num, 3, 1) = 'X'
        ) n
),
cte4 as
(
    select    input_num = isnull(n.input_num, c.input_num)
    from    cte3 c
        outer apply
        (
            select    input_num = stuff(input_num, 4, 1, n.num)
            from    numbers n    
            where    n.num between 0 and 9
            and    substring(input_num, 4, 1) = 'X'
        ) n
)
select    *
from    cte4



and the result for 1X3X is




1030
1031
1032
1033
1034
1035
1036
1037
1038
1039
1130
1131
1132



But the above method is restricted to max 4 numbers. I want to make it scalable, so recursive CTE came to my mind.




declare    @input_num varchar(10)
 
select    @input_num = 'X1X3'
 
; with 
cte as
(
    select    input_num = @input_num, pos = 0
 
    union all
 
    select    input_num = convert(varchar(10), isnull(n.input_num, c.input_num)), pos = pos + 1
    from    cte c
        outer apply
        (
            select    input_num = stuff(input_num, c.pos + 1, 1, n.num)
            from    numbers n    
            where    n.num between 0 and 9
            and    substring(input_num, c.pos + 1, 1) = 'X'
        ) n
    where    pos    < len(@input_num)
)
select    *
from    cte
where    pos    = len(@input_num)
order by input_num

With that, it is able to generate longer numbers. Of-course longer number will need more time to compute and the query will run longer.


But again, i wasn’t very happy with that query. It is looping every numbers. So i thought of using charindex to find the position of X and with that, i can change from OUTER to CROSS APPLY as i do not cater for non X char. And the query is also much simplify.



declare    @input_num varchar(10)
 
select    @input_num = 'X2X4X'
 
; with 
cte as
(
    select    input_num = @input_num, pos = charindex('X', @input_num)
 
    union all
 
    select    input_num = convert(varchar(10), n.input_num),
        pos       = charindex('X', n.input_num)
    from    cte c
        cross apply
        (
            select    input_num = stuff(input_num, c.pos, 1, n.num)
            from    numbers n    
            where    n.num between 0 and 9
        ) n
    where    pos    <> 0
)
select    input_num
from    cte
where    pos    = 0
order by input_num

Oh also, the first thing when i saw the thread is, OP want to buy 4 D numbers Winking smile

No comments:

Post a Comment