1。sql字符与位的互转
create function f_chg1 (@temp varchar(8))
returns char(1)
as
begin
declare @i int
declare @n int
declare @c char(1)
set @i = 0
set @n = 0
while @temp <> ''
begin
set @i = @i + power(2,@n) * cast(right(@temp,1) as int)
set @temp = left(@temp,len(@temp) -1 )
set @n = @n + 1
end
set @c = char(@i)
return(@c)
end
go
create function f_chg2 (@c char(1))
returns char(8)
as
begin
declare @temp varchar(8)
declare @i int
set @temp = ''
set @i = ascii(@c)
while @i > 0
begin
set @temp = @temp + cast(@i%2 as varchar(1))
set @i = ceiling( @i / 2 )
end
set @temp = REVERSE(@temp)
return(@temp)
end
go
select dbo.f_chg2('c')
--01100011
select dbo.f_chg1('01100011')
--c
select dbo.f_chg2(dbo.f_chg1('11111111'))
--11111111
2.进制的转换
--十进制转化为二进制数
CREATE FUNCTION Dec_Bin(@decimal INT)
RETURNS VARCHAR(20)
AS
BEGIN
DECLARE @bin VARCHAR(20)
SET @bin=''
WHILE @decimal!=0
BEGIN
SET @bin=CAST(@decimal%2 AS VARCHAR(20))+@bin
SET @decimal=@decimal/2
END
RETURN(@bin)
END
SELECT dbo.Dec_Bin(9)
--十进制转化为八进制数
CREATE FUNCTION Dec_Hex(@decimal INT)
RETURNS VARCHAR(20)
AS
BEGIN
DECLARE @bin VARCHAR(20)
SET @bin=''
WHILE @decimal!=0
BEGIN
SET @bin=CAST(@decimal%8 AS VARCHAR(20))+@bin
SET @decimal=@decimal/8
END
RETURN(@bin)
END
SELECT dbo.Dec_Hex(10)