进制转换

2017-12-21 11:34:08来源:cnblogs.com作者:FishParadise人点击

分享

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: 使用示范

最新文章

123

最新摄影

闪念基因

微信扫一扫

第七城市微信公众平台