=========================问个sql问题========================

[tiandiyuzhou1]

例如数据库中有一字段存储格式为88702013,112345678,31321321
怎么存个参数进去分别判断传入的参数是否有等于其中一个？
declare @canshu varchar(50)
set @phone = (select top 1 phone from table)
...

[叶子]

declare @canshu varchar(50)
set @canshu='88702013'
if(charindex(','+@canshu+',',','+'88702013,112345678,31321321+',')>0)
select '存在其中'

[叶子]

declare @canshu varchar(50)
set @canshu='88702013'
if(charindex(','+@canshu+',',','+'88702013,112345678,31321321'+',')>0)
select '存在其中'

修正一下

[tiandiyuzhou1]

[Quote=引用 1 楼 maco_wang 的回复:]
SQL code
declare @canshu varchar(50)
set @canshu='88702013'
if(charindex(','+@canshu+',',','+'88702013,112345678,31321321+',')>0)
select '存在其中'
[/Quote]谢谢回复，这个可行，不过我想用分割然后一个个判断的该怎么做呢？

[-晴天]

select * from tb where ','+columnname+',' like ','+@phone+','

sample

create table tb(c varchar(50))
insert into tb select '34,12,532'
go
declare @s varchar(50)
set @s='34'
select * from tb where ','+c+',' like '%,'+@s+',%'
go
drop table tb
/*
c
--------------------------------------------------
34,12,532

(1 行受影响)

*/

[tiandiyuzhou1]

[Quote=引用 4 楼 qianjin036a 的回复:]
SQL code
select * from tb where ','+columnname+',' like ','+@phone+','

sample

SQL code
create table tb(c varchar(50))
insert into tb select '34,12,532'
go
declare @s varchar(50)
set @s='34……
[/Quote]谢谢回复，这个可以，不过我想先把88702013,112345678,31321321这个按，分割，然后一个个判断的该怎么写呢？而且88702013,112345678,31321321这个逗号不固定几个！

[叶子]

[Quote=引用 5 楼 tiandiyuzhou1 的回复:]
引用 4 楼 qianjin036a 的回复:
SQL code
select * from tb where ','+columnname+',' like ','+@phone+','

sample

SQL code
create table tb(c varchar(50))
insert into tb select '34,12,532'
go
declare @……
[/Quote]

/*按照符号分割字符串*/
create function [dbo].[m_split](@c varchar(2000),@split varchar(2))   
  returns @t table(col varchar(200))   
  as   
    begin   
      while(charindex(@split,@c)<>0)   
        begin   
          insert @t(col) values (substring(@c,1,charindex(@split,@c)-1))   
            set @c = stuff(@c,1,charindex(@split,@c),'') 
           -- SET @c = substring(@c,charindex(' ',@c)+1,len(@c))     
        end   
      insert @t(col) values (@c)   
      return   
end
 
/*测试
select * from dbo.m_split('1,2,3,4,5',',')
*/

给你个函数，可以直接用，得到表。

[-晴天]

参考:

create table tb(c varchar(50))
insert into tb select '88702013,112345678,31321321'
create table tb1(id int,c varchar(50))
insert into tb1 select 1,'88702013'
insert into tb1 select 2,'31321321'
go
select * from tb1 k1 where exists(select 1 from (
select b.c from(
select c= CONVERT(xml,'<root><v>' + REPLACE([c], ',', '</v><v>') + '</v></root>') from tb
)a outer apply(
select c= N.v.value('.', 'varchar(100)') from a.[c].nodes('/root/v') N(v)
)b
)k2 where c=k1.c)
go
drop table tb,tb1
/*
id          c
----------- --------------------------------------------------
1           88702013
2           31321321

(2 行受影响)
*/

[-晴天]

说明一下:

create table tb(c varchar(50))
insert into tb select '88702013,112345678,31321321'
create table tb1(id int,c varchar(50))
insert into tb1 select 1,'88702013'
insert into tb1 select 2,'31321321'
insert into tb1 select 3,'8237593115'
go
select * from tb1 k1 where exists(select 1 from (
--下面这几行是拆分
select b.c from(
select c= CONVERT(xml,'<root><v>' + REPLACE([c], ',', '</v><v>') + '</v></root>') from tb
)a outer apply(
select c= N.v.value('.', 'varchar(100)') from a.[c].nodes('/root/v') N(v)
)b
--到这里,可以独立开来运行了试试
)k2 where c=k1.c)
go
drop table tb,tb1
/*
id          c
----------- --------------------------------------------------
1           88702013
2           31321321

(2 行受影响)
*/

[tiandiyuzhou1]

谢谢两位，得到我想要的结果了！

declare @str varchar(200)
set @str='1,2,3,4,5,6,7'
while(charindex(',',@str)<>0)
	begin
		print substring(@str,1,charindex(',',@str)-1)
		set @str=stuff(@str,1,charindex(',',@str),'')
	end

[-晴天]

declare @str varchar(200)
set @str='1,2,3,4,5,6,7'
select b.c from(
select c= CONVERT(xml,'<root><v>' + REPLACE(@str, ',', '</v><v>') + '</v></root>')-- from tb
)a outer apply(
select c= N.v.value('.', 'varchar(10)') from a.[c].nodes('/root/v') N(v)
)b
go
/*
c
----------
1
2
3
4
5
6
7

(7 行受影响)
*/