4/18/19

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

No comments:

Post a Comment