
string permutations
By: a guest on
Jan 9th, 2013 | syntax:
SQL | size: 0.55 KB | hits: 95 | expires: Never
DECLARE @a nvarchar(100) = 'abbccc';
WITH a AS(SELECT 1 n UNION ALL SELECT n+1 FROM a WHERE n<len(@a))
,b AS
(
SELECT CAST('' AS nvarchar(100)) s, CAST(@a AS nvarchar(100)) r, n p
FROM a
UNION ALL
SELECT
CAST(s+SUBSTRING(r,p,1) AS nvarchar(100)),
CAST(SUBSTRING(r,1,p-1)+SUBSTRING(r,p+1,999) AS nvarchar(100)),
n
FROM a,b
WHERE p<=len(r)
)
SELECT DISTINCT s
FROM b
WHERE len(s) = len(@a)
ORDER BY s
OPTION (maxrecursion 10000)