create function [dbo].[split]
(
@SourceSql varchar(8000),
@StrSeprate varchar(10)
)
returns @temp table(F1 varchar(100))
as
begin
    declare @i int
    set @SourceSql = rtrim(ltrim(@SourceSql))
    set @i = charindex(@StrSeprate,@SourceSql)
    while @i >= 1
    begin
        if len(left(@SourceSql,@i-1))>0
        begin
            insert @temp values(left(@SourceSql,@i-1))
        end
        set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)
        set @i=charindex(@StrSeprate,@SourceSql)
    end
    if @SourceSql <> ''
        insert @temp values(@SourceSql)
    return
end
 
select * from split(',,777,,11,,888,88,,1122,888,88,77,,00,,00',',')

------解决方案--------------------
1.select * from table1 where id in ('1,2,3')---结果不正确
2.select * from table1 where id in (1,2,3)---结果正确

对于1可以修改如下：

select * from table1 where ','+'1,2,3'+',' like '%,'+ltrim(id)+',%'

select * from table1 where charindex(','+ltrim(id)+',',','+'1,2,3'+',') > 0

------解决方案--------------------
探讨


引用:
in ('1,2,3')--只是把'1,2,3'当成一个字符串

--如下修改即可
select * from table1 where ltrim（id） in (select ids from table2)


这样修改后结果还是不对啊，去掉''结果才正确


------解决方案--------------------
可以用charindex函数。
select * from table1 where charindex(id,(select ids from table2))>0  
and charindex(id,(select ids from table3))=0

------解决方案--------------------
探讨


把字符串分隔了


------解决方案--------------------


SQL code

--CHARINDEX函数可以实现这个功能
select * from table1 where chaxindex(convert(varchar(20),id),(select ids from table2))>0;

------解决方案--------------------
探讨


in ('1,2,3')--只是把'1,2,3'当成一个字符串

--如下修改即可
select * from table1 where ltrim（id） in (select ids from table2)


------解决方案--------------------
e.g.
SQL code

use tempdb
go
if object_id('tempdb..#') Is not null
    Drop Table #

create table #(id int identity)
go
Insert Into # default values
go 200

--1
Select * from #  Where id in(1,2,3)
--2
Select * from #  Where patindex('%,'+rtrim(id)+',%',',1,2,3,')>0
--3
Declare @Input nvarchar(2000),@sql nvarchar(max)
Set @Input='1,2,3'
Set @sql=N'Select * From # Where id in('+@input+N')'
Exec sp_executesql @sql

/*
id
------------------
1
2
3
*/

------解决方案--------------------
用charindex即可。

------解决方案--------------------
SQL code

declare @sql varchar(6000)

select @sql='select * from table1 where id in ('
+(select ids from table2)
+') and id not in ('
+(select ids from table3)+');'

exec(@sql)