SQL SERVER – Remove Duplicate Chars From String – Part 2

I have already made a post on SQL SERVER – UDF – Remove Duplicate Chars From String where I showed you a method of removing duplicate characters form alphanumeric string. The UDF uses WHILE LOOP to extract the numbers.

Here is another method without using WHILE LOOP. It is done by using a Tally number table.

CREATE PROCEDURE REMOVE_DUPLICATE(@string VARCHAR(100))
AS
DECLARE @result VARCHAR(100)
SET @result=''
SELECT @result=@result+min(substring(@string ,number,1)) 
FROM
( 
    SELECT number 
	FROM master..spt_values 
	WHERE type='p' AND number BETWEEN 1 AND len(@string )
) as t 
GROUP BY substring(@string,number,1)
ORDER BY min(number)
SELECT @result 
GO

The logic is to split the characters into different rows and select minimum value for each value so that duplicates will be removed and concatenate them.

Now execute the above procedure by passing the string value.

EXEC REMOVE_DUPLICATE 'aaabbbbbcc111111111111112'

The result is abc12.

Well, that was it. We have an interesting solution. Let me know if you have a better solution. I will be happy to post the same with due credit to you.

Here are few additional blog posts which you may find interesting:

• SQL SERVER – Delete Duplicate Records – Rows
• SQL SERVER – Delete Duplicate Rows
• SQL SERVER – Select and Delete Duplicate Records – SQL in Sixty Seconds #036 – Video

Reference: Pinal Dave (https://darkslategrey-bat-805937.hostingersite.com)