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
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
and the result for 1X3X is
But the above method is restricted to max 4 numbers. I want to make it scalable, so recursive CTE came to my mind.
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.
Oh also, the first thing when i saw the thread is, OP want to buy 4 D numbers