现在有两个字符串:长度为31
'1111100000111111111100000111110'
'1110000110000001111100000111110'如何执行按位或操作
得到字符串
'1111100110111111111100000111110'
'1111100000111111111100000111110'
'1110000110000001111100000111110'如何执行按位或操作
得到字符串
'1111100110111111111100000111110'
select @s1='1111100000111111111100000111110'
, @s2='1110000110000001111100000111110'
declare @i int
set @i=charindex('0', @s1)
while @i>0 and @i<=len(@s1)
select @s1=stuff(@s1,@i,1,substring(@s2,@i,1)), @i=charindex('0', @s1, @i+1) select s=@s1
/*
s
--------------------------------
1111100110111111111100000111110
*/
create function fn_YiHuo(@s1 varchar(max), @s2 varchar(max))
returns varchar(max) as
begin
declare @i int
set @i=charindex('0', @s1)
while @i>0 and @i<=len(@s1)
select @s1=stuff(@s1,@i,1,substring(@s2,@i,1)), @i=charindex('0', @s1, @i+1) return @s1
end
godeclare @s1 varchar(32), @s2 varchar(32)
select @s1='1111100000111111111100000111110'
, @s2='1110000110000001111100000111110' select s=dbo.fn_YiHuo(@s1,@s2)drop function dbo.fn_YiHuo
/*
s
--------------------------------
1111100110111111111100000111110
*/
create function fn_bitOr(
@v1 varchar(64),@v2 varchar(64)
) returns varchar(64)
as begin
declare @rtn varchar(64)
declare @pos int
set @rtn = ''
set @pos = 0 declare @b1 int, @b2 int
while (@pos < len(@v1) and @pos < len(@v2)) begin
set @b1 = cast(substring(@v1,@pos+1,1)as int)
set @b2 = cast(substring(@v2,@pos+1,1)as int)
set @rtn = @rtn+cast((@b1 | @b2) as varchar)
set @pos = @pos + 1
end
return @rtn
end
godeclare @ta varchar(32) set @ta = '1111100000111111111100000111110'
declare @tb varchar(32) set @tb = '1110000110000001111100000111110'select dbo.fn_bitOr(@ta, @tb)
go
drop function fn_bitOr
go
create function fn_YiHuo(@s1 varchar(max), @s2 varchar(max))
returns varchar(max) as
begin
declare @result varchar(max), @i int
if len(@s1)>len(@s2)
select @result=@s1, @s1=@s2
else
select @result=@s2 set @i=charindex('0', @result)
while @i>0 and @i<=len(@s1)
select @result=stuff(@result,@i,1,substring(@s1,@i,1)), @i=charindex('0', @result, @i+1) return @result
end
godeclare @s1 varchar(32), @s2 varchar(32)
select @s1='1111100000111111111100000111110'
, @s2='1110000110000001111100000111110' select s=dbo.fn_YiHuo(@s1,@s2)
/*
s
--------------------------------
1111100110111111111100000111110
*/drop function dbo.fn_YiHuo
/** 按位或
*/
create function fn_bitOr(
@v1 varchar(64),@v2 varchar(64)
) returns varchar(64)
as begin
declare @rtn varchar(64)
declare @pos int
set @rtn = ''
set @pos = 0 declare @b1 int, @b2 int
while (@pos < len(@v1) and @pos < len(@v2)) begin
set @b1 = cast(substring(@v1,@pos+1,1)as int)
set @b2 = cast(substring(@v2,@pos+1,1)as int)
set @rtn = @rtn+cast((@b1 | @b2) as varchar)
set @pos = @pos + 1
end
return @rtn
end
go
/** 按位与
*/
create function fn_bitAnd(
@v1 varchar(64),@v2 varchar(64)
) returns varchar(64)
as begin
declare @rtn varchar(64)
declare @pos int
set @rtn = ''
set @pos = 0 declare @b1 int, @b2 int
while (@pos < len(@v1) and @pos < len(@v2)) begin
set @b1 = cast(substring(@v1,@pos+1,1)as int)
set @b2 = cast(substring(@v2,@pos+1,1)as int)
set @rtn = @rtn+cast((@b1 & @b2) as varchar)
set @pos = @pos + 1
end
return @rtn
end
godeclare @ta varchar(32) set @ta = '1111100000111111111100000111110'
declare @tb varchar(32) set @tb = '1110000110000001111100000111110'select dbo.fn_bitOr(@ta, @tb),dbo.fn_bitAnd(@ta, @tb)
go
drop function fn_bitOr,fn_bitAnd
go
set @b='1110000110000001111100000111110' declare @sql varchar(50)
declare @s table(id int identity(1,1),a int)insert @s(a) select top 31 0 from syscolumnsset @sql=''
select @sql=@sql+case when substring(@a,id,1)='1' or substring(@b,id,1)='1' then '1' else '0' end from @sselect @sql--result
/*------------------------------
111110011011111111110000011111(所影响的行数为 1 行)
*/
但是如果这样:
表T
f1 f2
a 10011100
a 00001101
b 10011100
b 00000001
b 00000101
b 10011100
c 10000000
c 10011100
我想 select f1 , 按位或(f2) from t group by f1
类似于聚合的函数,能写吗?
================
有解会+分。谢谢!
expression | expression------------
用一个简单的函数按位比较就行了..
insert a select 'a','10011100'
union all select 'a','00001101'
union all select 'b','10011100'
union all select 'b','00000001'
union all select 'b','00000101'
union all select 'b','10011100'
union all select 'c','10000000'
union all select 'c','10011100' go
create function funHB(@a varchar(50),@b varchar(50))
returns varchar(50)
as
begin
declare @sql varchar(50)
declare @s table(id int identity(1,1),a int)
insert @s(a) select top 30 0 from syscolumns
select @sql=isnull(@sql,'')+case when substring(@a,id,1)='1' or substring(@b,id,1)='1' then '1' else '0' end from @s where id<=len(@a)
return @sql
endgo
create function getData(@f1 varchar(10))
returns varchar(20)
begin
declare @num int
declare @a varchar(50),@b varchar(50),@result varchar(50)
select @num=count(1) from a where f1=@f1
if @num<2
set @result=@f1
else
begin
declare cur cursor for select f2 from a where f1=@f1
open cur
fetch next from cur into @a
fetch next from cur into @b
while @@fetch_status=0
begin
set @a=dbo.funHB(@a,@b)
fetch next from cur into @b
end
close cur
deallocate cur
set @result=@a
end
return @a
end
goselect f1,dbo.getData(f1) value from a group by f1--result
/*f1 value
---------- --------------------
a 10011101
b 10011101
c 10011100(所影响的行数为 3 行)*/