I can Remove Duplicate characters while preserving their first-occurrence order. This second method uses explicit positions and ordered aggregation.
-- SQL Server 2017+, compatibility level 110+ for ordered aggregation.
DECLARE @String varchar(100) = 'aaabbbbbcc111111111111112';
;WITH Digits(n) AS
(
SELECT n FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS d(n)
), Positions AS
(
SELECT a.n * 10 + b.n + 1 AS Position
FROM Digits AS a CROSS JOIN Digits AS b
), Characters AS
(
SELECT Position, SUBSTRING(@String, Position, 1) AS Character
FROM Positions
WHERE Position <= DATALENGTH(@String)
), FirstOccurrences AS
(
SELECT MIN(Position) AS FirstPosition, MIN(Character) AS Character
FROM Characters GROUP BY Character
)
SELECT STRING_AGG(CONVERT(varchar(max), Character), '')
WITHIN GROUP (ORDER BY FirstPosition) AS Result
FROM FirstOccurrences;
The input should produce abc12. I use visible tally positions instead of undocumented master..spt_values. Ordered STRING_AGG establishes the intended aggregation sequence. Repeated variable assignment across rows does not provide that ordering contract.
This version targets SQL Server 2017 or later, with the compatibility level needed by WITHIN GROUP. Grouping follows the column expression’s collation: under a case-insensitive collation, A and a can count as the same character. Decide those rules before using it as a general text-cleaning function.
The example is deliberately bounded to varchar(100) and simple single-byte characters. DATALENGTH includes trailing spaces but counts bytes; Unicode and multibyte encodings need a character-aware position strategy. For an empty input, the aggregate returns NULL unless you explicitly choose an empty-string replacement.
Reference: Ordered STRING_AGG.
Related reading
- SQL SERVER – UDF – Remove Duplicate Chars From String
- SQL SERVER – Delete Duplicate Records – Rows
- SQL SERVER – Delete Duplicate Rows
- SQL SERVER – Select and Delete Duplicate Records – SQL in Sixty Seconds #036 – Video
A character set is not an ordered string, it is a collection that needs an explicit reconstruction order.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
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.