SQL将数字转换为任何基（二进制，十六进制，...，triconta十六进制）的字符串表示形式 [英] SQL convert number to string representation of any base (binary, hexadecimal, ..., tricontahexadecimal)

问题描述

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

要求使用数字[0-9]和大写字符[AZ]，可用字符总数为36。

我需要将例如45转换为基数36，输出必须为19，或者使用任何范围基本形式2至36. 方案

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

这个过程是：
$ b $ ol

划分期望基数（在本例中为基数2）INTO您想要转换的数字。

用您在小学所做的余额书写商数（答案）。 重复这个划分使用前一个商数中的整数（余数前面的数字）进行处理。 继续重复这个划分unti l余数前面的数字只有零。

答案是从下往上读取的余数。

您可以在这里看到算法和更多示例 。

SQL中的函数是在SQL Server实例中全局有用的最佳选择，执行转换的代码如下所示：

IF OBJECT_ID（N'dbo.NUMBER_TO_STR_BASE'，N'FN'）不为空
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
，@ remaining int = 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 OR @remainder> 0）
BEGIN
SET @remainder = @dividend％@base; - 基数
SET @dividend = @dividend / @base中的分部编号提醒; - 除法的整数部分，成为下一个循环的新divident
IF（@dividend> 0或@remainder> 0） - 当商和提醒为0时，检查不对应最后一个循环
SET @numberString = CHAR（（CASE WHEN @remainder< = 9 THEN ASCII（'0'）ELSE ASCII（'A'） - 10 END）+ @remainder）+ @numberString;
END;
RETURN（@numberString）;
END
GO


执行上述代码后，您可以测试它们在任何查询中甚至在一个复杂的TSL代码中调用函数。
$ b

  SELECT dbo.NUMBER_TO_STR_BASE（ 16,45）AS'十六进制'; 
  -  45以16为基数（十六进制）为2D 
 SELECT dbo.NUMBER_TO_STR_BASE（2,45）AS'binary'; 
  -  45以2为基数（二进制）为101101 
 SELECT dbo.NUMBER_TO_STR_BASE（36,45）AS'tricontahexadecimal'; 
  -  45基数（tricontaexadecimal）是19 
 SELECT 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

这篇关于SQL将数字转换为任何基（二进制，十六进制，...，triconta十六进制）的字符串表示形式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！