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 (https://blog.sqlauthority.com)

, ,
Previous Post
SQL SERVER – Change Password of SA Login Using Management Studio
Next Post
SQLAuthority News – Thank You to Blog Readers

Related Posts

13 Comments. Leave new

  • 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

    Reply
  • 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

    Reply
  • 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.

    Reply
  • Kris,

    try this

    select * from table
    where ‘&’+@search_string+’&’ like ‘%&’+cast(col as varchar(10))+’&%’

    Reply
  • 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

    Reply
  • Vimal Saifudin
    January 8, 2009 12:51 pm

    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

    Reply
  • @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….

    Reply
  • thanks a lot it realy helps me.

    Reply
  • Hi,

    How to remove spaces in middle of the string after the comma in sql server 2005.

    Regards,
    V.Poongodi

    Reply
  • Hi,

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

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

    Reply
  • 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

    Reply
    • use the below function …

      create function Getcharcount
      (@string varchar(100))
      returns @data table(charname varchar(1),charcount int)
      As
      begin
      declare @len int
      declare @charlen int
      declare @char varchar(1)

      set @len=len(@string)
      while @len>0
      begin
      set @char=left(@string,1)
      set @string=replace(@string,@char,”)
      set @charlen=@len-len(@string)
      insert into @data
      select @char,@charlen
      set @len=len(@string)
      end
      return
      end

      select * from Getcharcount(‘aannsssoo’)

      Reply

Leave a Reply

Menu