SQL SERVER – UDF – Remove Duplicate Chars From String

Few days ago, I received following wonderful UDF from one of this blog reader. This UDF is written for specific purpose of removing duplicate chars string from one large string. Virendra Chauhan, author of this UDF is working as DBA in Lutheran Health Network.

CREATE FUNCTION dbo.REMOVE_DUPLICATE_INSTR
(@datalen_tocheck INT,@string VARCHAR(255))
RETURNS VARCHAR(255)
AS
BEGIN
DECLARE @str VARCHAR(255)
DECLARE @count INT
DECLARE @start INT
DECLARE @result VARCHAR(255)
DECLARE @end INT
SET @start=1
SET @end=@datalen_tocheck
SET @count=@datalen_tocheck
SET @str = @string
WHILE (@count <=255)
BEGIN
IF (@result IS NULL)
BEGIN
SET @result=''
END
SET @result=@result+SUBSTRING(@str,@start,@end)
SET @str=REPLACE(@str,SUBSTRING(@str,@start,@end),'')
SET @count=@count+@datalen_tocheck
END
RETURN @result
END
GO>, 1)
Usage:
SET CONCAT_NULL_YIELDS_NULL OFF

SELECT dbo.Remove_duplicate_instr(<CHARacter length OF a

duplicate SUBSTRING >,<string contain duplicate>)
Example:
To keep char set in a string unique and remove duplicate 3 char long string run this UDF as inline function.

SET CONCAT_NULL_YIELDS_NULL OFF

SELECT dbo.Remove_duplicate_instr(3,f123456789123456456
Resultset:
123456789

Reference : Pinal Dave (https://blog.sqlauthority.com), Virendra Chauhan (DBA)