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 (http://blog.SQLAuthority.com), Virendra Chauhan (DBA)

About these ads

5 thoughts on “SQL SERVER – UDF – Remove Duplicate Chars From String

  1. Hello sir,
    I am new to DB and sql server 2005, this site is very helpful,
    thanks a lot.
    Sir I am using this function ‘Removing duplicate chars string from one large string’,
    I found that while loop (while @count<=255) in this function is unnecessarily checking the condition even after removing duplicates, what I think is, it should come out of the loop when string doesn’t contain any duplicate chars.

    Like when I am calling the function:-

    SET CONCAT_NULL_YIELDS_NULL OFF
    SELECT dbo.Remove_duplicate_instr(3,123456789123456456)

    According to the function @count = 3 and it is checking the condition till @count = 258,
    But we get the output at @count =12 for the above and it should come out of the loop at
    @count = 16.

    If I am using if condition

    if (@str = ”)
    break

    After SET @count=@count+@datalen_tocheck
    Iam getting the output @count=12 and it is coming out of the loop at @count=16 . I think by this, we can avoid repeated checkings after removing duplicates.

    Is it not a performance issue?
    Just tell me sir whether I am correct or not.

    thanks,
    srikanth.

  2. Pingback: SQL SERVER - Remove Duplicate Characters From a String Journey to SQL Authority with Pinal Dave

  3. Pingback: SQL SERVER – Weekly Series – Memory Lane – #006 « SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s