Generate all possible permutations of a given character set with a variable length in t-sql

Question

I have written the script below to generate all possible permutations of a specified set of characters.

Is there a way to efficiently do this while allowing the length to be specified at runtime?

DECLARE @string NVARCHAR(MAX)
SELECT @string = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';

DECLARE @Chars TABLE (
    C CHAR(1) PRIMARY KEY
)

DECLARE @N INT 
SET @N = 1 
WHILE @N <= LEN(@string) BEGIN
    INSERT @Chars (
        C
    ) VALUES (
        SUBSTRING(@string, @N, 1)
    )

    SET @N = @N + 1
END

--SELECT * FROM @Chars


SELECT
    A.C + B.C + C.C
FROM @Chars A, @Chars B, @Chars C
ORDER BY
    A.C,
    B.C,
    C.C

Answer 1

If you are using SQL Server 2005 or later version, you could try a recursive CTE:

DECLARE @length int;
SET @length = 3;

WITH expanded AS (
  SELECT
    C = SUBSTRING(@string, N, 1)
  FROM numbers
  WHERE number BETWEEN 1 AND LEN(@string)
),
permutations AS (
  SELECT
    S = CAST(C AS nvarchar(max)),
    L = 1
  FROM expanded
  UNION ALL
  SELECT
    S = S + C,
    L = L + 1
  FROM permutations p
  CROSS JOIN expanded e
  WHERE L < @length
)
SELECT *
FROM permutations
WHERE L = @length
;

Here numbers is an auxiliary table of numbers, used to expand the string into a column of single characters.

Without more changes, this query would work with @length values up to 100 without issues. If you want permutations of greater length, you'll need to append this line:

OPTION (MAXRECURSION n)

where n is an integer value from 0 to 32767 indicating the maximum number of iterations of the recursive CTE, and the 100 mentioned above is the default. Nil actually means no limitation, which should probably be used with care.

You can try this query (and play with it) at SQL Fiddle, where I reduced the @string to just 8 characters (to be able to specify more different values for @length without making the query to return too many rows) and also defined the numbers table as a subset of a system table master..spt_values.