DECLARE @INPUT nvarchar(100) = 'abbccc';
WITH nums AS
(
SELECT n = 1 -- base case starting at 1
UNION ALL
SELECT n = n+1 -- recursive case: next number = 2, 3, ... len(@input)
FROM nums
WHERE n < len(@INPUT) -- stop and don't run if n equal the len(@input)
)
,perms AS
(
SELECT -- base case for recursion
string = CAST('' AS nvarchar(100)), -- string we are generating, start as empty string
remain = CAST(@INPUT AS nvarchar(100)), -- remaining characters to append to our string
pos = n -- position of next character in remaining string to append to base case
FROM nums
UNION ALL
-- recursive case where we append 1 character per step
SELECT
-- append character at position pos to base string
string = CAST(string + SUBSTRING(remain,pos,1) AS nvarchar(100)),
-- remove character we added from remaining characters
remain = CAST(stuff(remain,pos,1,'') AS nvarchar(100)),
-- all possible positions of remaining characters
pos = nums.n
FROM perms, nums
-- only look at possible valid positions
-- this forces the recursion to terminate when remain is empty
WHERE pos <= len(remain)
)
SELECT DISTINCT string
FROM perms
WHERE len(string) = len(@INPUT)
ORDER BY string
OPTION (maxrecursion 10000)