Journey to SQL Authority with Pinal Dave

Posted in SQL, SQL Authority, SQL Function, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology | 5 Comments

5 Responses to “SQL SERVER - Remove Duplicate Characters From a String”

1. on December 31, 2007 at 10:32 am kris

   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. on December 31, 2007 at 6:24 pm Madhivanan

   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. on January 1, 2008 at 8:58 pm kris

   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. on February 15, 2008 at 6:15 pm Madhivanan

   Kris,

   try this

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

   
   
5. on February 27, 2008 at 6:35 pm karthikeyan N

   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