A function to parse ranges

Here’s a quick function to parse a list of ranges (in a varchar variable) into a table of ranges. Might come in practical, for instance when parsing user arguments for a report.

In this article, I’ll let the code and comments do the talking. Here’s the code for the table value function:

CREATE FUNCTION dbo.fn_parseRange(
    @list                 varchar(max),
    @listSeparator        char(1)=',',
    @intervalSeparator    char(1)='-'
)
RETURNS @tbl TABLE (
    rangeNo        int IDENTITY(1, 1) NOT NULL,
    rangeStart     varchar(100) NOT NULL,
    rangeEnd       varchar(100) NOT NULL,
    PRIMARY KEY CLUSTERED (rangeNo)
)
AS

BEGIN;
    --- Declarations, start values:
    DECLARE @range varchar(max);
    SET @list=NULLIF(@list, '');

    --- Start of loop:
    WHILE (@list IS NOT NULL) BEGIN;
        --- @range is the current interval in the list
        SET @range=LEFT(@list,
            CHARINDEX(@listSeparator,
                @list+@listSeparator)-1);

        --- Truncate @list
        SET @list=NULLIF(
            SUBSTRING(@list,
                CHARINDEX(@listSeparator,
                    @list+@listSeparator)+1,
                LEN(@list)), '');

        --- Insert the current @range into the output table:
        INSERT INTO @tbl (rangeStart, rangeEnd)
        SELECT
            LEFT(@range,
                CHARINDEX(@intervalSeparator,
                    @range+@intervalSeparator)-1),
            SUBSTRING(@range,
                CHARINDEX(@intervalSeparator, @range)+1,
                LEN(@range));
    END;

    RETURN;
END;

And here’s how it’s used:

SELECT *
FROM dbo.fn_parseRange('a0-a9,b-c,d,e,f-h', ',', '-');

And of course, you could use it in a report by joining it in like a table:

...
FROM dbo.FactTable AS fact
INNER JOIN dbo.AccountDim AS acct ON
    fact.Account=acct.[ID]
INNER JOIN
    dbo.fn_parseRange('1011-1099,1500-1520,1599',
        ',', '-') AS rng ON
            rng.rangeStart<=acct.AccountNumber AND
            rng.rangeEnd>=acct.AccountNumber
...