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
GOThe 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://blog.sqlauthority.com)
6 Comments. Leave new
DECLARE @result VARCHAR(100)
SET @result=”;
with cte(result ,string,lvl)
as
(
select cast( SUBSTRING(@string,1,1) as varchar(max)) , REPLACE(@string,SUBSTRING(@string,1,1),”),1
union all
select result+SUBSTRING(string,1,1) , REPLACE(string,SUBSTRING(string,1,1),”), cte.lvl+1 from cte where LEN(string)>0
)
select top 1 result from cte order by lvl desc
Great post! Thanx for sharing.
Dear Pinal,
Superb Post. Thanks for posting it.
Could you please clarify my doubt in your Query, I get the same output while using the following query ( just removed min function from your query).
DECLARE @string varchar(100)=’trytrytryaaaabbbbbbbccccccc’
DECLARE @result VARCHAR(100)
SET @result=”
SELECT @result=@result+substring(@string ,number,1) //Here modified the query
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
Is that Min function is needed for any other scenario?
Hi Sir,
How will it fare using while loop as below:
declare @string varchar(200)=’jsajhdkasdwqewasd’,@result varchar(100)=”,@i int =2
select @result=”
select @result=left(@string,1)
while @i<=len(@string)
Begin
if charindex(substring(@string,@i,1),@result)=0
select @result=@result+substring(@string,@i,1)
select @i=@i+1
End
select @result
Which one would be faster?
what is master..spt_values in the from clause?
These solutions expect a character to never reoccur later in the string.
So AAAABBBAAA yields AB instead of ABA which is not what is desired in repeating string removal tasks.