dbo.digits() - a sample In-line TVF using CTE

I found a question in one of the online forums where a user wanted to generate digits, like for an counter, but control how many digits and a range of allowed digits.

ex. I want 2 digits, and only the digits between 3 and 5 giving me a result like this.

digits
  33
  34
  35
  43
  44
  45
  53
  54
  55

I came up with this solution...

CREATE FUNCTION dbo.digits(
@len int,
@min int,
@max int
)
RETURNS TABLE
AS
RETURN
WITH
digits (digit) AS (
SELECT '0' UNION ALL SELECT '1' UNION ALL SELECT '2' UNION ALL
SELECT '3' UNION ALL SELECT '4' UNION ALL SELECT '5' UNION ALL
SELECT '6' UNION ALL SELECT '7' UNION ALL SELECT '8' UNION ALL
SELECT '9'
),
selection AS (
SELECT
digit
FROM digits
WHERE
CAST(digit AS int) BETWEEN @min AND @max
),
result AS (
SELECT
0 AS position,
CAST(digit as varchar(max)) digit
FROM selection
UNION ALL
SELECT
r.position + 1,
CONCAT(s.digit,r.digit)
FROM result r
CROSS JOIN selection s
WHERE r.position < @len
)
SELECT
digit AS digits
FROM result
WHERE position = @len - 1

You then execute the TVF with this line:
SELECT * FROM dbo.digits(2, 3, 5) ORDER BY 1

Changes

This is another not about SQL post. As of march 1st of 2019 I'm working for Axians IoT Operations and are adding value on top of their amazing solution microServiceBus, which connects any device to any IOT hub. Or most major ones at least.

For me this is about changing life, and adding a balance between work and life. In todays' ever changing world it is crucial to reflect over life. I did for sure and am truly grateful for this opportunity.

I will get back to blogging, answering questions at forums, and interact with the community as well as product groups. I will also start blueprinting the book I've been thinking about for years.

More to come...

Yours truly,
Mattias