create function bin2dec(@s varchar(255) ) --二进制转化为十进制 returns int as begin declare @i int, @temp char(1), @result int select @i=1 select @result=0 while (@i<=len(@s)) begin select @temp=substring(@s,@i,1) select @result=@result+ (ascii(@temp)-48)*power(2,len(@s)-@i) select @i=@i+1 end return @result endcreate function dec2bin(@n int ) --十进制转化为二进制 returns varchar(255) as begin declare @i int,@temp int, @s varchar(255) set @i=@n set @s='' while (@i>0) begin set @temp=@i % 2 set @i=@i /2 set @s=ltrim(@temp)+@s end return @s end select [位与]=dbo.dec2bin(dbo.bin2dec(11001100)&dbo.bin2dec(11110000))位与 ------------------- 11000000select [位或]=dbo.dec2bin(dbo.bin2dec(11001100)|dbo.bin2dec(11110000))位或 ------------------ 11111100
给一个字符操作的函数: CREATE FUNCTION fn_CalcBin(@cN1 VARCHAR(8000),@cN2 VARCHAR(8000),@Sign VARCHAR(5)) RETURNS VARCHAR(8000) AS BEGIN DECLARE @Result VARCHAR(8000) DECLARE @M INT DECLARE @TMaxc VARCHAR(8000),@TMinc VARCHAR(8000) DECLARE @i INT
IF ISNULL(@cN1,'')='' OR ISNULL(@cN2,'')='' SET @Result=ISNULL(@cN1,'')+ISNULL(@cN2,'') ELSE BEGIN SELECT @M=CASE WHEN LEN(@cN1)>LEN(@cN2) THEN LEN(@cN1) ELSE LEN(@cN2) END, @TMaxc=CASE WHEN LEN(@cN1)>LEN(@cN2) THEN @cN1 ELSE @cN2 END, @TMinc=CASE WHEN LEN(@cN1)>LEN(@cN2) THEN REPLICATE('0',LEN(@cN1)-LEN(@cN2))+@cN2 ELSE REPLICATE('0',LEN(@cN2)-LEN(@cN1))+@cN1 END
SELECT @i=1,@Result='' WHILE @i<=@M BEGIN SET @Result=@Result+ CASE WHEN @Sign='&' THEN LTRIM(CAST(SUBSTRING(@TMaxc,@i,1) AS INT) & CAST(SUBSTRING(@TMinc,@i,1) AS INT)) WHEN @Sign='|' THEN LTRIM(CAST(SUBSTRING(@TMaxc,@i,1) AS INT) | CAST(SUBSTRING(@TMinc,@i,1) AS INT)) WHEN @Sign='^' THEN LTRIM(CAST(SUBSTRING(@TMaxc,@i,1) AS INT) ^ CAST(SUBSTRING(@TMinc,@i,1) AS INT)) END SET @i=@i+1 END END
| 按位或
--二进制转化为十进制
returns int
as
begin
declare @i int, @temp char(1), @result int
select @i=1
select @result=0
while (@i<=len(@s))
begin
select @temp=substring(@s,@i,1)
select @result=@result+ (ascii(@temp)-48)*power(2,len(@s)-@i)
select @i=@i+1
end
return @result
endcreate function dec2bin(@n int )
--十进制转化为二进制
returns varchar(255)
as
begin
declare @i int,@temp int, @s varchar(255)
set @i=@n
set @s=''
while (@i>0)
begin
set @temp=@i % 2
set @i=@i /2
set @s=ltrim(@temp)+@s
end
return @s
end
select [位与]=dbo.dec2bin(dbo.bin2dec(11001100)&dbo.bin2dec(11110000))位与
-------------------
11000000select [位或]=dbo.dec2bin(dbo.bin2dec(11001100)|dbo.bin2dec(11110000))位或
------------------
11111100
CREATE FUNCTION fn_CalcBin(@cN1 VARCHAR(8000),@cN2 VARCHAR(8000),@Sign VARCHAR(5))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Result VARCHAR(8000)
DECLARE @M INT
DECLARE @TMaxc VARCHAR(8000),@TMinc VARCHAR(8000)
DECLARE @i INT
IF ISNULL(@cN1,'')='' OR ISNULL(@cN2,'')=''
SET @Result=ISNULL(@cN1,'')+ISNULL(@cN2,'')
ELSE
BEGIN
SELECT @M=CASE WHEN LEN(@cN1)>LEN(@cN2) THEN LEN(@cN1) ELSE LEN(@cN2) END,
@TMaxc=CASE WHEN LEN(@cN1)>LEN(@cN2) THEN @cN1 ELSE @cN2 END,
@TMinc=CASE WHEN LEN(@cN1)>LEN(@cN2) THEN REPLICATE('0',LEN(@cN1)-LEN(@cN2))+@cN2
ELSE REPLICATE('0',LEN(@cN2)-LEN(@cN1))+@cN1 END
SELECT @i=1,@Result=''
WHILE @i<=@M
BEGIN
SET @Result=@Result+
CASE WHEN @Sign='&' THEN
LTRIM(CAST(SUBSTRING(@TMaxc,@i,1) AS INT) & CAST(SUBSTRING(@TMinc,@i,1) AS INT))
WHEN @Sign='|' THEN
LTRIM(CAST(SUBSTRING(@TMaxc,@i,1) AS INT) | CAST(SUBSTRING(@TMinc,@i,1) AS INT))
WHEN @Sign='^' THEN
LTRIM(CAST(SUBSTRING(@TMaxc,@i,1) AS INT) ^ CAST(SUBSTRING(@TMinc,@i,1) AS INT))
END
SET @i=@i+1
END
END
RETURN @Result
ENDGO SELECT dbo.fn_CalcBin('11001100' ,'11110000','&') [与],
dbo.fn_CalcBin('11001100' ,'11110000','|') [或],
dbo.fn_CalcBin('11001100' ,'11110000','^') [异或]--result
/*与 或 异或
------------------------------ ------------------------------ ------------------------------
11000000 11111100 00111100(所影响的行数为 1 行)*/