欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

进制转换

程序员文章站 2022-03-23 11:17:13
1. 前言 最近在整理关于字符集的笔记(整理后可能会做成一个系列的博客,敬请留意:)),其中涉及不同进制之间转换的运算。所以干脆就把几种常用的进制换算做成函数,方便直接调用。以下函数均以T-SQL的语法实现。 2. 列表 1. 前言 2. 列表 十进制转二进制 十进制转八进制 十进制转十六进制 十进 ......
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: 使用示范