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)
hi little help here plz,
i have table with the data like below:
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.
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
thanks for your reply. the query is returning the values like :
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.
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)
if charindex( @c,@rst,1)=0
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
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
declare @list1 varchar(max), @Pos int
set @list = ltrim(rtrim(@list))+@Delim
Set @pos = charindex(@delim, @list, 1)
while @pos > 0
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)
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
Let me know if if your problem has been solved by this….
thanks a lot it realy helps me.
How to remove spaces in middle of the string after the comma in sql server 2005.
Post some sample data with expected result
I have a string that has repeating chars
I mean ‘vvabcdcbb’
I want to only find the one vv, bb
please suggest me.
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
returns @data table(charname varchar(1),charcount int)
declare @len int
declare @charlen int
declare @char varchar(1)
insert into @data
select * from Getcharcount(‘aannsssoo’)