Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Server Citation - SQL Blog by Hemantgiri S. Goswami, SQL MVP

Hemantgiri S. Goswami is working as a Consultant at Surat, INDIA. He has been a Microsoft SQL Server MVP three years running; he also moderates multiple SQL Server community forums including http://www.sql-server-performance.com and http://www.sqlserver-qa.net. He actively participates and speaks at local user group events organized under the aegis of www.surat-user-group.org, DotNetChaps and CSI Surat chapters, of which he is a founding and active member. He regularly publishes article on his blog space http://www.sql-server-citation.com. He has recently taken up a new initiative - blogging about SQL in his native language Gujarat through his blog http://sqlservercitation-gujarati.blogspot.com. He is an Author of the book SQL Server 2008 High Availability.

SQL Server – Generating PERMUTATIONS using T-Sql

Were you ever asked to generate string Permutations using TSql? I was recently asked to do so, and the logic which I could manage to come up at that point is shared in the below script.

DECLARE @Value AS VARCHAR(20) = 'ABCC' --Mention the text which is to be permuted
DECLARE @NoOfChars AS INT = LEN(@Value)
DECLARE @Permutations TABLE (Value VARCHAR(20)) --Make sure the size of this Value is equal to your input string length (@Value)
;

WITH NumTally
AS (
--Prepare the Tally Table to separate each character of the Value.
SELECT 1 Num

UNION ALL

SELECT Num + 1
FROM NumTally
WHERE Num < @NoOfChars
),
Chars
AS (
--Separate the Characters
SELECT Num,
SUBSTRING(@Value, Num, 1) Chr
FROM NumTally
)
--Persist the Separated characters.
INSERT INTO @Permutations
SELECT Chr
FROM Chars

--Prepare Permutations
DECLARE @i AS INT = 1

WHILE (@i < @NoOfChars)
BEGIN
--Store the Permutations
INSERT INTO @Permutations
SELECT DISTINCT --Add DISTINCT if required else duplicate Permutations will be generated for Repeated Chars.
P1.Value + P2.Value
FROM (
SELECT Value
FROM @Permutations
WHERE LEN(Value) = @i
) P1
CROSS JOIN (
SELECT Value
FROM @Permutations
WHERE LEN(Value) = 1
) P2

--Increment the Counter.
SET @i += 1

--Delete the Incorrect Lengthed Permutations to keep the table size under control.
DELETE
FROM @Permutations
WHERE LEN(Value) NOT IN (
1,
@i
)
END

--Delete InCorrect Permutations.
SET @i = 1

WHILE (@i <= @NoOfChars)
BEGIN
--Deleting Permutations which has not used "All the Chars of the given Value".
DELETE
FROM @Permutations
WHERE Value NOT LIKE '%' + SUBSTRING(@Value, @i, 1) + '%'

--Deleting Permutations which have repeated incorrect character.
DELETE
FROM @Permutations
WHERE LEN(Value) - LEN(REPLACE(Value, SUBSTRING(@Value, @i, 1), '')) != LEN(@Value) - LEN(REPLACE(@Value, SUBSTRING(@Value, @i, 1), ''))

SET @i += 1
END

--Selecting the generated Permutations.
SELECT Value
FROM @Permutations

Hope, this script helps!


Please share your suggestions if you have any to improve this logic.

Comments

Leave a comment on the original post [feedproxy.google.com, opens in a new window]

Loading comments...