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
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
我下面的例子是取出‘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
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'
(select de from test1 where id='a01') like '%'+rtrim(id)+'%')
a01换成个参数
where charindex(rtrim(ltrim(a.id)),rtrim(ltrim(b.de)))>=1
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