进制转换

时间：2024-01-23 17:32:11

1. 背景

最近在整理关于字符集的笔记（整理后可能会做成一个系列的博客，敬请留意：）），其中涉及不同进制之间转换的运算。所以干脆就把几种常用的进制换算做成函数，方便直接调用。以下函数均以T-SQL的语法实现。
 

2. 列表

  • 十进制转二进制
  • 十进制转八进制
  • 十进制转十六进制
  • 十进制转三十六进制
  • 二进制转十进制
  • 八进制转十进制
  • 十六进制转十进制
  • 三十六进制转十进制
其中非十进制之间的转换，比如二进转十六进制，或十六进制转二进制，都可以通过先成十进后再转。所以只要把这搞定这几个转换，基本上常用的进制转换都实际了。
 
 

 十进制转二进制

CREATE FUNCTION [dbo].[ufn_bigint2bin] ( @value BIGINT )
RETURNS VARCHAR(64)
/*十进制转换为二进制*/
AS
    BEGIN
        DECLARE @seq CHAR(2);
        DECLARE @result VARCHAR(64);
        DECLARE @digit CHAR(1);
        DECLARE @power INT;

        SET @power = 2;   
        SET @seq = '01';
        SET @result = SUBSTRING(@seq, ( @value % @power ) + 1, 1);

        WHILE @value > 0
            BEGIN
                SET @digit = SUBSTRING(@seq, ( ( @value / @power ) % @power ) + 1, 1);
                SET @value = @value / @power;
                IF @value <> 0
                    SET @result = @digit + @result;
            END; 

        RETURN @result;
    END;
GO

Code-1: 十进制转二进制

 

 

十进制转八进制

CREATE FUNCTION [dbo].[ufn_bigint2oct] ( @value BIGINT )
RETURNS VARCHAR(64)
/*十进制转换为八进制*/
AS
    BEGIN
        DECLARE @seq CHAR(8);
        DECLARE @result VARCHAR(64);
        DECLARE @digit CHAR(1);
        DECLARE @power INT;

        SET @power = 8;    
        SET @seq = '01234567';
        SET @result = SUBSTRING(@seq, ( @value % @power ) + 1, 1);

        WHILE @value > 0
            BEGIN
                SET @digit = SUBSTRING(@seq, ( ( @value / @power ) % @power ) + 1, 1);
                SET @value = @value / @power;
                IF @value <> 0
                    SET @result = @digit + @result;
            END; 

        RETURN @result;
    END;
GO

Code-2: 十进制转八进制

 

 

十进制转十六进制

1. 内置函数

/*注意值不能超过@i<=2147483647（7FFFFFFF）*/
DECLARE @i INT = 255;
SELECT CONVERT(VARBINARY, @i);
SELECT CAST(@i AS VARBINARY);
SELECT  master.dbo.fn_varbintohexstr(@i);
GO

 Code-3: 内置函数十进制转十六进制

 

/*如果@i>2147483647，最大不能超过9223372036854775807（7FFFFFFFFFFFFFFF）*/
DECLARE @i BIGINT= 4294967295;
SELECT CONVERT(VARBINARY, @i);
SELECT CAST(@i AS VARBINARY);
SELECT  master.dbo.fn_varbintohexstr(@i);
GO

 Code-4: 内置函数十进制转十六进制

  
/*如果直接输入具体数值，@i<=2147483647*/
SELECT CONVERT(VARBINARY, 2147483647);
SELECT CAST(2147483647 AS VARBINARY);
SELECT  master.dbo.fn_varbintohexstr(2147483647);
GO

 Code-5: 内置函数十进制转十六进制

 

2. 自定义函数

CREATE FUNCTION [dbo].[ufn_bigint2hex] ( @value BIGINT )
RETURNS VARCHAR(64)
/*十进制转换为十六进制*/
AS
    BEGIN
        DECLARE @seq CHAR(16);
        DECLARE @result VARCHAR(64);
        DECLARE @digit CHAR(1);
        DECLARE @power INT;
        
        SET @power = 16;
        SET @seq = '0123456789ABCDEF';
        SET @result = SUBSTRING(@seq, ( @value % @power ) + 1, 1);

        WHILE @value > 0
            BEGIN
                SET @digit = SUBSTRING(@seq, ( ( @value / @power ) % @power ) + 1, 1);
                SET @value = @value / @power;
                IF @value <> 0
                    SET @result = @digit + @result;
            END; 

        RETURN @result;
    END;
GO

Code-6: 十进制转十六进制

 

 

十进制转三十六进制

CREATE FUNCTION [dbo].[ufn_bigint236hex] ( @value BIGINT )
RETURNS VARCHAR(64)
/*十进制转换为三十六进制*/
AS
    BEGIN
        DECLARE @seq CHAR(36);
        DECLARE @result VARCHAR(64);
        DECLARE @digit CHAR(1);
        DECLARE @power INT;

        SET @power = 36;    
        SET @seq = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';
        SET @result = SUBSTRING(@seq, ( @value % @power ) + 1, 1);

        WHILE @value > 0
            BEGIN
                SET @digit = SUBSTRING(@seq, ( ( @value / @power ) % @power ) + 1, 1);
                SET @value = @value / @power;
                IF @value <> 0
                    SET @result = @digit + @result;
            END; 

        RETURN @result;
    END;
GO

Code-7: 十进制转三十六进制

 

 

二进转十进制

CREATE FUNCTION [dbo].[ufn_bin2bigint] ( @bin_string VARCHAR(63) )
RETURNS BIGINT
/* 二进制转十进制 */
AS
    BEGIN 
        DECLARE @result BIGINT;

        DECLARE @i INT
           ,@len INT;

        DECLARE @power BIGINT;
        SET @power = 2;

        SELECT  @i = 0
               ,@result = 0
               ,@bin_string = RTRIM(LTRIM(UPPER(@bin_string)));

        SET @len = LEN(@bin_string); 

        IF ( @len = 63 )
            BEGIN 
                IF ( UNICODE(SUBSTRING(@bin_string, 1, 1)) > 49 )
                    BEGIN 
                        RETURN NULL;
                    END; 
            END; 

        WHILE ( @i < @len )
            BEGIN 
                IF (
                     ( SUBSTRING(@bin_string, @len - @i, 1) NOT BETWEEN '0' AND '1' )
                   )
                    BEGIN 
                        SET @result = NULL;
                        BREAK;
                    END; 
 
                SET @result = @result + ( CHARINDEX(SUBSTRING(@bin_string, @len - @i, 1), '01') - 1 )
                              * CAST(POWER(@power, @i) AS BIGINT);
                SET @i = @i + 1; 
            END; 
 
        RETURN @result; 
    END;
GO

Code-8: 二进制转十进制

 

 

八进制转十进制

CREATE FUNCTION [dbo].[ufn_oct2bigint] ( @oct_string VARCHAR(21) )
RETURNS BIGINT
/*八进制转十进制*/
AS
    BEGIN 
        DECLARE @result BIGINT;

        DECLARE @i INT
           ,@len INT;

        DECLARE @power BIGINT;
        SET @power = 8;

        SELECT  @i = 0
               ,@result = 0
               ,@oct_string = RTRIM(LTRIM(UPPER(@oct_string)));

        SET @len = LEN(@oct_string); 

        IF ( @len = 21 )
            BEGIN 
                IF ( UNICODE(SUBSTRING(@oct_string, 1, 1)) > 55 )
                    BEGIN 
                        RETURN NULL;
                    END; 
            END; 

        WHILE ( @i < @len )
            BEGIN 
                IF (
                     ( SUBSTRING(@oct_string, @len - @i, 1) NOT BETWEEN '0' AND '7' )                     
                   )
                    BEGIN 
                        SET @result = NULL;
                        BREAK;
                    END; 
 
                SET @result = @result + ( CHARINDEX(SUBSTRING(@oct_string, @len - @i, 1), '01234567') - 1 )
                              * CAST(POWER(@power, @i) AS BIGINT);
                SET @i = @i + 1; 
            END; 
 
        RETURN @result; 
    END;
GO

Code-9: 八进制转十进制

 

 

十六进制转十进制

1. 内置函数

/*16进制是偶数位的，并且不包含前缀0x*/
DECLARE @s VARCHAR(16) = 'FF';
SELECT  CONVERT(BIGINT, CONVERT(VARBINARY, CAST(N'0x' + @s AS VARCHAR), 1));

 Code-10: 内置函数十六进制转十进制

 

2. 自定义函数

CREATE FUNCTION [dbo].[ufn_hex2bigint] ( @hex_string VARCHAR(16) )
RETURNS BIGINT
/*十六进制转十进制*/
AS
    BEGIN 
        DECLARE @result BIGINT;

        DECLARE @i INT
           ,@len INT;

        DECLARE @power BIGINT;
        SET @power = 16;

        SELECT  @i = 0
               ,@result = 0
               ,@hex_string = RTRIM(LTRIM(UPPER(@hex_string)));

        SET @len = LEN(@hex_string); 

        IF ( @len = 16 )
            BEGIN 
                IF ( UNICODE(SUBSTRING(@hex_string, 1, 1)) > 55 )
                    BEGIN 
                        RETURN NULL;
                    END; 
            END; 

        WHILE ( @i < @len )
            BEGIN 
                IF (
                     ( SUBSTRING(@hex_string, @len - @i, 1) NOT BETWEEN '0' AND '9' )
                     AND ( SUBSTRING(@hex_string, @len - @i, 1) NOT BETWEEN 'A' AND 'F' )
                   )
                    BEGIN 
                        SET @result = NULL;
                        BREAK;
                    END; 
 
                SET @result = @result + ( CHARINDEX(SUBSTRING(@hex_string, @len - @i, 1), '0123456789ABCDEF') - 1 )
                              * CAST(POWER(@power, @i) AS BIGINT);
                SET @i = @i + 1; 
            END; 
 
        RETURN @result; 
    END;
GO

Code-11: 十六进制转十进制

 

 

三十六进制转十进制

CREATE FUNCTION [dbo].[ufn_36hex2bigint] ( @thirtysix_string VARCHAR(13) )
RETURNS BIGINT
/*三十六进制转十进制*/
AS
    BEGIN 
        DECLARE @result BIGINT;

        DECLARE @i INT
           ,@len INT;

        DECLARE @power BIGINT;
        SET @power = 36;

        SELECT  @i = 0
               ,@result = 0
               ,@thirtysix_string = RTRIM(LTRIM(UPPER(@thirtysix_string)));

        SET @len = LEN(@thirtysix_string); 

        IF ( @len = 13 )
            BEGIN 
                IF ( UNICODE(SUBSTRING(@thirtysix_string, 1, 1)) > 49 )
                    BEGIN 
                        RETURN NULL;
                    END; 
            END; 

        WHILE ( @i < @len )
            BEGIN 
                IF (
                     ( SUBSTRING(@thirtysix_string, @len - @i, 1) NOT BETWEEN '0' AND '9' )   
                     AND ( SUBSTRING(@thirtysix_string, @len - @i, 1) NOT BETWEEN 'A' AND 'Z' )                
                   )
                    BEGIN 
                        SET @result = NULL;
                        BREAK;
                    END; 
 
                SET @result = @result + ( CHARINDEX(SUBSTRING(@thirtysix_string, @len - @i, 1), '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ') - 1 )
                              * CAST(POWER(@power, @i) AS BIGINT);
                SET @i = @i + 1; 
            END; 
 
        RETURN @result; 
    END;
GO

Code-12: 三十六进制转十进制

 

 

3. 使用示范

--十进转其他进制
SELECT  dbo.ufn_bigint2bin(255) AS bigint2bin
       ,dbo.ufn_bigint2oct(255) AS bigint2oct
       ,dbo.ufn_bigint2hex(255) AS bigint2hex
       ,dbo.ufn_bigint236hex(255) AS bigint236hex;

--其他进制转十进制
--注意传入参数是字符型的
SELECT  dbo.ufn_bin2bigint('11111111') AS bin2bigint
       ,dbo.ufn_oct2bigint('377') AS oct2bigint
       ,dbo.ufn_hex2bigint('FF') AS hex2bigint
       ,dbo.ufn_36hex2bigint('73') AS thirty_six_2bigint;

--二进制转十六进制
SELECT  dbo.ufn_bigint2hex(dbo.ufn_bin2bigint('11111111')) AS bin2hex;
GO

Code-13: 使用示范

 

Figure-1: 使用示范

 

 

相关文章