create function f_int2bin_1(@i int)
returns int
as
begin
  declare @s varchar(1000)
  set @s=''
  while @i>0
    select @s=cast(@i%2 as varchar)+@s,@i=@i/2
  declare @s1 as char(1)
  declare @s2 as char(1)
  declare @j as int
  declare @k as int
  set @j = 0
  set @k = 0
  set @s1 = ''
  set @i = 1
  while @i <= len(@s)
  begin
    set @s2 = substring(@s , @i , 1)
    if @s2 <> @s1
       begin
       set @s1 = @s2
       if @j > @k and @s2 = '0'
          set @k = @j
       set @j = 1
       end
    else
       begin
       if @i = len(@s) and @s2 = '1'
          begin
          if @j + 1 > @k
             set @k = @j + 1
          end
       else
          set @j = @j + 1
       end
    set @i = @i + 1    
  end
  return @k
end
go

--调用 
select dbo.f_int2bin_1(3516)
/*
110110111100
4
*/

select dbo.f_int2bin_1(35160)
/*
1000100101011000
2
*/

select dbo.f_int2bin_1(351600)
/*
1010101110101110000
3
*/

drop function dbo.f_int2bin_1

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

探讨


先循环用'00'替换成'0' ,直接没有连接的两个0
然后用XML分解串，最后直接统计最长的串