如何循环判断字符串？

[uffeng]

表A中 字段"id" “DE” 两个
DE中 保存的是字符串 就ID的集合 如
ID de
a01
a02 A01
B04 A02A01
现在我取出任意ID出 如取出B04这记录，如何判断其字段中 是否存在其他的ID字段

也就是说 我要判断出B04中DE字段 到底存在几个其他ID
如何写SQL

[leus]

id的长度是否固定？如果固定就好说，否则比较困难，我只能想到一个笨办法，一个个找

[Dennis618]

declare @n nvarchar(1000)
declare @i int
declare @sumI int
set @sumI=0
set @n=(select de from test1 where id='b04')
set @i=1
while @i+2<=len(@n)
begin
set @sumI=@sumI +(select count(*) from table1 where id=substring(@n,@i,3))

set @i=@i+3
end
select @sumI

[Dennis618]

只能是固定长度罗，否则很难找。

[pengdali]

select * from 表 tem where de<>'' and de is not null exists(select 1 from 表 where id='B04' and tem.de like '%'+de+'%')

[yujohny]

如果你每个ID开头都是A-z的字母一个，而且其它都是数字，那就可以参考下面的方法
我下面的例子是取出‘A01a02A03’中的字母，结果是‘AaA’
select top 8000 identity(int,1,1) as N into #numtab from
(select top 100 id=1 from sysobjects) as a,
(select top 100 id=1 from sysobjects) as b,
(select top 100 id=1 from sysobjects) as c

declare @str varchar(1000),@ReturnStr varchar(1000)
select @str='A01a02A03'
set @ReturnStr=''

select @ReturnStr=@ReturnStr+substring(a.strnum,b.N,1) from
(select @str as strNum)a left join #numtab b on
charindex(substring(a.strnum,b.N,1),a.strnum,n)=b.n
where substring(a.strnum,b.N,1) like '[A-z]'
drop table #numtab
select @ReturnStr

[yujohny]

那你就可以把他改为自定义函数，当然要先用这个语句建立一个表numtab
select top 8000 identity(int,1,1) as N into numtab from
(select top 100 id=1 from sysobjects) as a,
(select top 100 id=1 from sysobjects) as b,
(select top 100 id=1 from sysobjects) as c
然后再写函数
create function getZm(@ID nvarchar(50))
returns nvarchar(100)
as
begin
declare @str varchar(1000),@ReturnStr varchar(1000)
select @str=de from 表 where ID =@ID
set @ReturnStr=''

select @ReturnStr=@ReturnStr+substring(a.strnum,b.N,1) from
(select @str as strNum)a left join numtab b on
charindex(substring(a.strnum,b.N,1),a.strnum,n)=b.n
where substring(a.strnum,b.N,1) like '[A-z]'

return @ReturnStr
end

然后再用这个语句查询
select len(dbo.getZm('B04'))
或select len(dbo.getZm(ID)) from 表 where ID='B04'

[pengdali]

写错了：

select * from 表 tem where exists(select 1 from 表 where id='B04' and de like like '%'+tem.id+'%')

[leus]

select count(*) from test1 t where [id] in (select id from test1 where
(select de from test1 where id='a01') like '%'+rtrim(id)+'%')
a01换成个参数

[leus]

比大力慢了

[orcale]

select a.id from test1 a,(select de from test1 where id='b04') b
where charindex(rtrim(ltrim(a.id)),rtrim(ltrim(b.de)))>=1

[orcale]

哈哈,給分

[Dennis618]

declare @n nvarchar(18)
declare @i int
set @i=0
declare cur1 cursor for
select id from test1
open cur1
fetch next from cur1 into @n
while @@fetch_status=0
begin
set @i=@i+(select count(*) from test1 where de like '%'+rtrim(@n)+'%' and id='b04')
fetch next from cur1 into @n
end
close cur1
deallocate cur1
select @i