SQL 将数字转换为任何基数的字符串表示形式(二进制、十六进制、...、十六进制)

如何使用 SQL 将数字转换为所需数字基数的字符串表示形式，例如将 45 转换为基数 2(二进制)、8(八进制)、16(十六进制)、..36.

要求使用数字[0-9]和大写字符[A-Z]，总共36个字符.

例如，我需要将 45 转换为基数 36，输出必须是19"，或者使用任何基数形式 2 到 36.

解决方案

这是将数字转换为字符串表示为任何数字基数的解决方案.解决方案是在 SQL Server 上运行的函数，它接收基数和数字参数.第一个参数是您要获取的基数，第二个参数是您要转换的数字.

流程是:

1. 将所需"基数(在本例中为基数 2)除以您要转换的数字.
2. 像小学时那样写出商(答案)和余数.
3. 使用前一个商的整数(余数前面的数字)重复此除法过程.
4. 继续重复此除法，直到余数前面的数字仅为零.
5. 答案是自下而上读取的余数.

您可以在此处查看算法和更多示例.

>

SQL 中的函数是使它们在 SQL Server 实例中全局有用的最佳选择，进行转换的代码如下:

IF OBJECT_ID (N'dbo.NUMBER_TO_STR_BASE', N'FN') IS NOT NULL删除函数 dbo.NUMBER_TO_STR_BASE;去创建函数 dbo.NUMBER_TO_STR_BASE (@base int,@number int)返回 varchar(MAX)以调用者身份执行作为开始声明 @dividend int = @number,@剩余整数 = 0,@numberString varchar(MAX) = CASE WHEN @number = 0 THEN '0' ELSE '' END ;SET @base = CASE WHEN @base <= 36 THEN @base ELSE 36 END;--最大基数为36，包括[0-9A-Z]的范围WHILE (@dividend > 0 或 @remainder > 0)开始SET @remainder = @dividend % @base ;--base中的分区号提醒SET @dividend = @dividend/@base ;-- 除法的整数部分，成为下一个循环的新除法IF(@dividend > 0 OR @remainder > 0)--当商和提醒为0时检查不对应最后一个循环SET @numberString = CHAR( (CASE WHEN @remainder <= 9 THEN ASCII('0') ELSE ASCII('A')-10 END) + @remainder ) + @numberString;结尾;返回(@numberString)；结尾去

执行上述代码后，您可以测试它们在任何查询甚至是复杂的 TSL 代码中调用该函数.

SELECT dbo.NUMBER_TO_STR_BASE(16,45) AS 'hexadecimal';-- 45 in base 16(hexadecimal) 是 2D选择 dbo.NUMBER_TO_STR_BASE(2,45) 作为二进制"；-- 45 in base 2(binary) 是 101101选择 dbo.NUMBER_TO_STR_BASE(36,45) 作为十六进制"；-- 45 in base (tricontaexadecimal) 是 19选择 dbo.NUMBER_TO_STR_BASE(37,45) AS 'tricontahexadecimal-test-max-base';-- 输出将是 19，因为最大基数是 36，-- 对应字符 [0-9A-Z]

欢迎评论或提出改进建议，希望对你有用

How to convert a number to it string representation for a desired numeric base using SQL, for example convert 45 to the base 2(binary), 8(octantal),16(hexadecimal), ..36.

The requirement is to use the numbers [0-9] and the uppercase characters [A-Z], the total of available characters is 36.

I need to convert for example 45 to base 36, the output must be "19", or use any range base form 2 to 36.

解决方案

This is the solution made to convert a number to the string representation to any numeric base. The solution is a function that run on SQL Server, it receives the base and number parameter. The first one parameter is the base number that you want to get and the second one parameter is the number that you want to convert. The algorithm used was taken from the site mathbits.com .

Using the same example from the site of the algorithm, if you want to convert 5 base 10 into base 2.

The process is:

1. Divide the "desired" base (in this case base 2) INTO the number you are trying to convert.
2. Write the quotient (the answer) with a remainder like you did in elementary school.
3. Repeat this division process using the whole number from the previous quotient (the number in front of the remainder).
4. Continue repeating this division until the number in front of the remainder is only zero.
5. The answer is the remainders read from the bottom up.

You can see the algorithm and more examples here.

A function in SQL is the best choice to make them useful globally in the SQL Server Instance, the code to do the conversion is the following:

IF OBJECT_ID (N'dbo.NUMBER_TO_STR_BASE', N'FN') IS NOT NULL
    DROP FUNCTION dbo.NUMBER_TO_STR_BASE;
GO
CREATE FUNCTION dbo.NUMBER_TO_STR_BASE (@base int,@number int)
RETURNS varchar(MAX)
WITH EXECUTE AS CALLER
AS
BEGIN
     DECLARE @dividend int = @number
        ,@remainder int = 0 
        ,@numberString varchar(MAX) = CASE WHEN @number = 0 THEN '0' ELSE '' END ;
     SET @base = CASE WHEN @base <= 36 THEN @base ELSE 36 END;--The max base is 36, includes the range of [0-9A-Z]
     WHILE (@dividend > 0 OR @remainder > 0)
         BEGIN
            SET @remainder = @dividend % @base ; --The reminder by the division number in base
            SET @dividend = @dividend / @base ; -- The integer part of the division, becomes the new divident for the next loop
            IF(@dividend > 0 OR @remainder > 0)--check that not correspond the last loop when quotient and reminder is 0
                SET @numberString =  CHAR( (CASE WHEN @remainder <= 9 THEN ASCII('0') ELSE ASCII('A')-10 END) + @remainder ) + @numberString;
     END;
     RETURN(@numberString);
END
GO

After you execute the above code, you can test them calling the function in any query or even in a complex TSL code.

SELECT dbo.NUMBER_TO_STR_BASE(16,45) AS 'hexadecimal';
-- 45 in base 16(hexadecimal) is 2D 
SELECT dbo.NUMBER_TO_STR_BASE(2,45) AS 'binary';
-- 45 in base 2(binary) is 101101
SELECT dbo.NUMBER_TO_STR_BASE(36,45) AS 'tricontahexadecimal';
-- 45 in base (tricontaexadecimal) is 19
SELECT dbo.NUMBER_TO_STR_BASE(37,45) AS 'tricontahexadecimal-test-max-base';
--The output will be 19, because the maximum base is 36,
-- which correspond to the characters [0-9A-Z]

Feel free to comment or suggest improvements, i hope it to be useful