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 **0**103, 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