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

## No comments:

## Post a Comment