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)
Is your SQL Server running slow and you want to speed it up without sharing server credentials? In my Comprehensive Database Performance Health Check, we can work together remotely and resolve your biggest performance troublemakers in less than 4 hours.
Once you learn my business secrets, you will fix the majority of problems in the future.
Have you ever opened any PowerPoint deck when you face SQL Server Performance Tuning emergencies? SQL Server Performance Tuning Practical Workshop is my MOST popular training with no PowerPoint presentations and 100% practical demonstrations.
Essentially I share my business secrets to optimize SQL Server performance.
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
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
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.
Kris,
try this
select * from table
where ‘&’+@search_string+’&’ like ‘%&’+cast(col as varchar(10))+’&%’
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
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
@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….
thanks a lot it realy helps me.
Hi,
How to remove spaces in middle of the string after the comma in sql server 2005.
Regards,
V.Poongodi
Post some sample data with expected result
Hi,
I have a string that has repeating chars
I mean ‘vvabcdcbb’
I want to only find the one vv, bb
please suggest me.
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
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’)