SQL Server进制

在项目中，大家可能都遇到过，需要把十进制转换为其他进制的情况，google上一搜，已经有很多2进制、8进制、16进制和十进制的转换方法。但是在一些项目中，这些可能无法满足要求，可能需要17、18甚至是32、36进制和十进制的转换，那么我们应该怎么办呢？不可能为每一种进制都去写一个函数，那样可不是明智之举。所以我这里提供一个十进制与N进制之间的互转函数（N<=32）。

N进制函数

1、准备工作

在写N进制函数之前，需要有一个用于存储表示N进制字符的基础表，这里我用一个表函数表示：

CREATE FUNCTION xavi.fn_NSystemTable()
RETURNS @temp TABLE (id SMALLINT IDENTITY, [Char] CHAR(1),[Ascii] SMALLINT)
AS
BEGIN
    DECLARE @ignoreAscii TABLE ([Ascii] SMALLINT)

    DECLARE @i INT
    SET @i = 58
    WHILE(@i <= 64)
    BEGIN
        INSERT INTO @ignoreAscii VALUES (@i)
        SET @i = @i + 1
    END

    SET @i = 0
    WHILE(@i < 43)
    BEGIN
        
        IF NOT EXISTS (SELECT 1 FROM @ignoreAscii WHERE [Ascii] = @i + 48)
        BEGIN
            INSERT INTO @temp VALUES (CHAR(@i + 48),@i + 48)
        END
            
        SET @i = @i + 1
    END
    RETURN
END

2、十进制转换为N进制

CREATE  FUNCTION xavi.fn_DecimalToNSystem (@bigInt BIGINT, @n TINYINT)
RETURNS VARCHAR(100)
AS 
BEGIN
    Declare @result VARCHAR(100),@mode INT,@remainder INT, @iRet CHAR(1)
    SELECT @mode = @bigInt, @result = '' 

    WHILE(1 = 1)
    BEGIN
        IF(@bigInt = 0 OR @n = 0 OR @n = 1)
        BEGIN
            SET @result = CONVERT(VARCHAR(100),@bigInt)
            BREAK
        END
        
        IF(@mode = 0)
        BEGIN
            BREAK
        END
        
        SET @remainder = @mode % @n
        SET @mode = @mode / @n    
        
        SELECT @iRet = [Char] FROM xavi.fn_NSystemTable() ns WHERE ns.id = @remainder + 1
        SET @result = @iRet + @result
    END
    
    RETURN @result
END

3、N进制转换为十进制

CREATE  FUNCTION xavi.fn_NSystemToDecimal (@nSys VARCHAR(100), @n TINYINT)
RETURNS BIGINT
AS
BEGIN
    Declare @result int,@iPos int,@iTmp int
    Select @result = 0,@iPos = 0
    While(@iPos <   Len(@nSys))
    BEGIN
        SELECT @iTmp = ns.id - 1 FROM xavi.fn_NSystemTable() ns WHERE ns.[Char] = SUBSTRING(@nSys,LEN(@nSys) - @iPos,1)
           
        Set @result = @result + @iTmp * POWER(CAST(@n AS BIGINT),cast(@iPos AS BIGINT))
        Set @iPos = @iPos + 1
    END
    
    RETURN @result
END

注意：目前测试下来对于最高进制（36进制），最多支持13位，但是我想这也足够了，因为36进制所能表示的范围远比10进制的13位数大得多，0<=y<=36 * 36¹²+ 36 * 36¹¹+......+ 36 * 36¹+ 36。所以一个N进制来说能表示的范围应该为：0<=y<=N * N^x+ N * N^x-1+......+ N * N¹+ N。

如何使用

那么我们应该怎么使用这些函数呢，这里举一个自增36进制字段的表的例子。

首先创建一个表：

CREATE TABLE xavi.tb_Test
(
    ID CHAR(10) PRIMARY KEY,
    Account VARCHAR(20),
    [Name] NVARCHAR(10)
)

然后创建一个触发器：

CREATE TRIGGER xavi.tr_TestInsert
ON xavi.tb_Test
INSTEAD OF INSERT 
AS

SET NOCOUNT ON

DECLARE @maxID BIGINT,
        @n TINYINT,
        @nSystemChar VARCHAR(10)
SET @n = 36
SELECT @maxID = ISNULL(MAX(xavi.fn_NSystemToDecimal(ID,@n)),0) FROM xavi.tb_Test
SET @nSystemChar = xavi.fn_DecimalToNSystem(@maxID + 1, @n)

INSERT INTO xavi.tb_Test(ID,Account,[Name])
SELECT    REPLICATE('0',10 - LEN(@nSystemChar)) + @nSystemChar,
        Account,
        [Name]
FROM INSERTED

接着往这个表里插入100条册数数据：

DECLARE @i INT
SET @i = 1
WHILE(@i <= 100)
BEGIN
    INSERT INTO xavi.tb_Test
    VALUES(@i,LEFT(REPLACE(CONVERT(VARCHAR(100),NEWID()),'-',''),10),LEFT(REPLACE(CONVERT(VARCHAR(100),NEWID()),'-',''),10))
    
    SET @i = @i + 1
END

执行看下表里的数据，可以得到如下图的结果：

从这个结果应该可以观察到，ID这一列已经是36进制的表示形式了。

扩展用法

有了这个N进制函数，那么我们再生产一些唯一编码、订单号等一些编码时，就可以用更少的位数，表示更大的范围。