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.

DECLARE @result VARCHAR(100)
SET @result=''
SELECT @result=@result+min(substring(@string ,number,1)) 
    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 

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:

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL Function, SQL Scripts, SQL Server, SQL String
Previous Post
SQL SERVER – How to Find UNIQUE Key Columns? – sp_special_columns
Next Post
SQL SERVER – How to Create Linked Server to SQL Azure Database?

Related Posts

5 Comments. Leave new

  • DECLARE @result VARCHAR(100)
    SET @result=”;
    with cte(result ,string,lvl)
    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
    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?

  • chinny krishna
    November 3, 2017 9:59 pm

    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)
    if charindex(substring(@string,@i,1),@result)=0
    select @result=@result+substring(@string,@i,1)
    select @i=@i+1
    select @result

    Which one would be faster?

  • what is master..spt_values in the from clause?


Leave a Reply

Exit mobile version