2012年10月22日星期一

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)ASBEGIN    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    RETURNEND

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 @resultEND

3、N进制转换为十进制

CREATE  FUNCTION xavi.fn_NSystemToDecimal (@nSys VARCHAR(100), @n TINYINT)RETURNS BIGINTASBEGIN    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 @resultEND

注意：目前测试下来对于最高进制（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_TestInsertON xavi.tb_TestINSTEAD OF INSERT ASSET NOCOUNT ONDECLARE @maxID BIGINT,        @n TINYINT,        @nSystemChar VARCHAR(10)SET @n = 36SELECT @maxID = ISNULL(MAX(xavi.fn_NSystemToDecimal(ID,@n)),0) FROM xavi.tb_TestSET @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 INTSET @i = 1WHILE(@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 + 1END

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

N System Test Result

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

扩展用法

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

TAG: