SQL SERVER – Remove Duplicate Characters From a String

Follow up of my previous article of Remove Duplicate Chars From String here is another great article written by Madhivanan where similar solution is suggested with alternate method of Number table approach. Check out Remove duplicate characters from a string

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

13 thoughts on “SQL SERVER – Remove Duplicate Characters From a String

  1. hi little help here plz,

    i have table with the data like below:

    ids_cloumn
    ========================
    1 , 2 , 4 , 23 , 12 , 11 , 9 , 8 , 7 , 10 ,
    1 , 3 , 4 , 21 , 16 , 12 , 19 , 10 ,
    2 , 3 , 6 , 24 , 15 , 18 , 9 , 8 ,
    3 , 5 , 8 , 27 , 14 , 19 , 39 , 7 , 13 ,
    1 , 7 , 9 , 28 , 19 , 11 , 29 , 8 ,

    i am having a list of nos with me (coming from another query)
    ex1: 1, 3 & 23
    ex2 : 1, 6, 34 & 23

    now i want to retrieve the list of rows which is having any of the nos.

    is there any other way with out using “LIKE %”

    If i am using like i need to write so many OR conditions based on the inputs.

    thanks

  2. kris,

    You need to split the values return from another query and check againt table column. Search for Split + sql server in google

    Also I suggest you to read about normalisation

  3. Hi Madhivanan,

    thanks for your reply. the query is returning the values like :
    1
    3
    23

    So there is no need to split the values.

    Actually i didnt design the database, somebody else did. I was just asked to help regarding this issue.

    Right now they are constructing a dinamic query in the program by using LIKE based on the query results and then passing it to SQL SERVER.

    many LIKEs in the query is really slowing down the process. So i want to know is there any other way to make it fast.

  4. this is also one type of method
    also following transfered like sp or function

    declare @i int
    declare @c char
    declare @rst varchar(8000)
    set @i=1
    set @rst=substring(‘aaassrt’,1,1)
    set @c=”

    while @i<=len(‘aaassrt’)
    begin
    set @c=substring(‘aaassrt’,@i,1)
    if charindex( @c,@rst,1)=0
    set @rst=@rst+@c
    set @i=@i+1
    end
    select @rst

  5. I have string @positive = 342,34,456,34,3454,456,
    Can anyone help me to remove the duplicate values from the above string to @positive=342,34,456,3454

  6. @Vimal

    You can use following funtion to remove duplicate entry from string….
    First I have convert values from list to table then select distinct then convert table to string…

    CREATE FUNCTION dbo.udf_List2Table
    (
    @List VARCHAR(MAX),
    @Delim CHAR
    )
    RETURNS
    @ParsedList TABLE
    (
    item VARCHAR(MAX)
    )
    AS
    BEGIN
    declare @list1 varchar(max), @Pos int
    set @list = ltrim(rtrim(@list))+@Delim
    Set @pos = charindex(@delim, @list, 1)
    while @pos > 0
    Begin
    set @list1 = ltrim(rtrim(left(@list, @pos – 1)))
    if @list1 ”
    Insert into @ParsedList values (cast(@list1 as varchar(max)))
    set @list = substring(@list, @pos+1, len(@list))
    set @pos = charindex(@delim, @list, 1)
    end
    Return

    end

    declare @list varchar(max)
    select @list = coalesce(@list+’,’,”) + A.item from (SELECT distinct item FROM dbo.udf_List2Table(’342,34,456,34,3454,456,’,’,’)) as A
    print @list

    Let me know if if your problem has been solved by this….

  7. Pingback: SQL SERVER - Remove Duplicate Entry from Comma Delimited String - UDF Journey to SQL Authority with Pinal Dave

  8. Hi,

    I have a string that has repeating chars
    I mean ‘vvabcdcbb’

    I want to only find the one vv, bb
    please suggest me.

  9. string=”aannsssoo”
    can any one help to find number of repeated char from this sting.
    my answer should be like this a-2time,n-2time,s-3time,o-2times

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