2008年1月29日 星期二

16進制轉成2進制

ALTER FUNCTION [dbo].[16Transfer2] (@Code Nvarchar(20))
RETURNS Nvarchar(100)
AS
BEGIN
 DECLARE @ResultVar Nvarchar(100)
 DECLARE @iLen Int
 DECLARE @I Int
 DECLARE @SingleCode Nvarchar(2)
 DECLARE @Ten INT

 SELECT @iLen = LEN(@Code)
 SET @I = 1
 SET @ResultVar = ''

 While @I <= @iLen
 BEGIN
  SET @SingleCode = SUBSTRING(@Code, @I, 1)

  SET @Ten = CASE @SingleCode
        WHEN 'A' THEN 10
        WHEN 'B' THEN 11
        WHEN 'C' THEN 12
        WHEN 'D' THEN 13
        WHEN 'E' THEN 14
        WHEN 'F' THEN 15
        ELSE CONVERT(INT, @SingleCode)
       END

  SET @ResultVar = @ResultVar + dbo.[10Transfer2_16](@Ten)
  SET @I = @I + 1

 END

 RETURN RTRIM(LTRIM(@ResultVar))
END

EX:Select dbo.[16Transfer2]('F')==>1111

把10進位的數字變成2進位--數字為0-15(16進制轉成2進制用)

ALTER FUNCTION [dbo].[10Transfer2_16] (@Code Int) RETURNS Nvarchar(4)
AS
BEGIN
 DECLARE @Number Int
 DECLARE @I INT
 DECLARE @Two Nvarchar(4)

 SET @Number = @Code
 SET @I = 3 --只除3次
 SET @Two = ''

 WHILE @I <> 0
 BEGIN
  SET @Two = CONVERT(Nvarchar(1), @Number%2) + @Two
  SET @Number = @Number / 2
  SET @I = @I - 1
 END

 IF @I = 0
 BEGIN
  SET @Two = CONVERT(Nvarchar(1), @Number) + @Two
 END

 RETURN LTRIM(RTRIM(@Two))
END

EX:Select dbo.[10Transfer2_16](14)==> 1110

2進制轉成10進制(function)

CREATE FUNCTION [dbo].[2Transfer10] (@Code Nvarchar(100)) RETURNS Int
AS
BEGIN
 DECLARE @ResultVar Int
 DECLARE @iLen Int
 DECLARE @I Int
 DECLARE @J INT
 DECLARE @Number Int
 
 SET @iLen = LEN(@Code)
 SET @I = @iLen-1
 SET @J = 1
 SET @ResultVar = 0

 WHILE (@I >= 0) AND (@iLen >= @J)
 BEGIN
  SET @Number = SUBSTRING(@Code, @J, 1)
  IF @Number = 1
  Begin
   SET @ResultVar = @ResultVar + POWER(2,@I)
  END
  SET @I = @I - 1
  SET @J = @J + 1
 END

 RETURN @ResultVar
END

EX:
  Select [dbo].[2Transfer10](1101) ==>13