假设某一字段 field1 内容如下
000101
101001
......怎么求出"1"在field1字符串中的位置,要求返回如下
select xxfunc(field1) from aaa where ...
4,6
1,3,6万分感谢,只有75分了
000101
101001
......怎么求出"1"在field1字符串中的位置,要求返回如下
select xxfunc(field1) from aaa where ...
4,6
1,3,6万分感谢,只有75分了
你寫個程序不是很好解決嗎
干嘛非得用sql
create table T(col1 varchar(20))
insert T select '000101'
insert T select '101001'create function fun(@col1 varchar(20))
returns varchar(1000)
as
begin
declare @re varchar(1000)
set @re=''
while(charindex('1', @col1)>0)
begin
set @re=@re+rtrim(charindex('1', @col1))+','
set @col1=stuff(@col1, charindex('1', @col1), 1, '0')
end if(@re<>'')
set @re=left(@re, len(@re)-1) return @re
endselect dbo.fun(col1) from T
--result
4,6
1,3,6(2 row(s) affected)
create table test(field varchar(100))
insert test select '000101'
union all select '1010001001'
union all select '1001001'
union all select '10001001'
create function dbo.xxfunc(@str varchar(1000),@char varchar(1))
returns varchar(1000)
as
begin
declare @re_str varchar(1000),@i int
set @re_str=''
set @i=0
while @i<len(@str)
begin
if charindex(','+cast(charindex(@char,@str,@i) as varchar(10))+',',@re_str+',')=0
set @re_str=@re_str+','+cast(charindex(@char,@str,@i) as varchar(10))
set @i=@i+1
end
return stuff(@re_str,1,1,'')
end
goselect dbo.xxfunc(field,'1') from testdrop table test
create table test(field varchar(100))
insert test select '11'
union all select '1010001001'
union all select '1001001'
union all select '10001001'
create function xxfunc(@str varchar(1000),@char varchar(1))
returns varchar(1000)
as
begin
declare @re_str varchar(1000),@i int
set @re_str=''
set @i=1
while @i<=len(@str)
begin
if charindex(','+cast(charindex(@char,@str,@i) as varchar(10))+',',@re_str+',')=0
set @re_str=@re_str+','+cast(charindex(@char,@str,@i) as varchar(10))
set @i=@i+1
end
return stuff(@re_str,1,1,'')
end
goselect dbo.xxfunc(field,'1') from testdrop table test
我不是很懂sql server 的t-sql
akuzou(那种动物没有头部?) 的function 比较难看懂,整理一下你的,大概是这个意思
declare @pos int,@count;
declare @re varchar(1000);
set @pos=0;
set @count=0;
set @re="";
while @pos<len(@str)
begin
if(charindex(@char,@str,@pos)>0)
begin
set @count=@count+1;
set @re=@re+cast(@count as varchar)+",";
set @pos=@pos+1;
end
return @re;
end