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