SQL Server 進位轉換函式

標籤：style   blog   http   color   io   os   使用   ar   strong   

原文:SQL Server 進位轉換函式

一、背景

前段時間群裡的朋友問了一個問題：“在查詢時增加一個遞增序列，如：0x00000001，即每一個都是36進位(0—9，A--Z)，0x0000000Z後面將是0x00000010，產生一個像下面的映射表“：

（Figure1：）

 

二、十進位轉換為十六進位

在網上有很多資料關於使用SQL語句把十進位轉換為十六進位的資料，比如：

--方式1SELECT CONVERT(VARBINARY(50), 23785)

執行傳回值為0x00005CE9，但是需要注意的是，這本應該返回二進位的，但是二進位估計是閱讀起來太麻煩，所以SQL Server 返回了十六進位，如果你想要儲存為字串並不是簡單把這直接使用CONVERT，類似下面的SQL是不會有傳回值的：

SELECT CONVERT(VARCHAR,CONVERT(VARBINARY(50), 23785))

所以網上出現了如下的函數來轉換為十六進位的字串：

--方式2CREATE FUNCTION Binary2HexStr(@bin VARBINARY(8000))RETURNS VARCHAR(8000)ASBEGIN    DECLARE @re VARCHAR(8000),@i INT    SELECT @re=‘‘,@i=datalength(@bin)    WHILE @i>0        SELECT @re=substring(‘0123456789ABCDEF‘,substring(@bin,@i,1)/16+1,1)                +substring(‘0123456789ABCDEF‘,substring(@bin,@i,1)%16+1,1)                +@re            ,@i=@i-1    RETURN(‘0x‘+@re)ENDGO--測試SELECT dbo.Binary2HexStr(23785)

上面的SQL同樣返回0x00005CE9，但是這次返回的是字串了，貌似問題都得以解決了，但是，只要你INT值不超過2147483648，這個問題就會出現了，執行下面的SQL返回的結果如Figure2所示：

SELECT dbo.Binary2HexStr(2147483647)SELECT dbo.Binary2HexStr(2147483648)

（Figure2：資料對比）

從進位的轉換運算出發，改進了宋沄劍寫了的SQL指令碼，修改參數為BIGINT類型：

--方式3CREATE FUNCTION BigInt2HexStr(@value BIGINT)RETURNS VARCHAR(50)ASBEGIN    DECLARE @seq CHAR(16)    DECLARE @result VARCHAR(50)    DECLARE @digit CHAR(1)        SET @seq = ‘0123456789ABCDEF‘    --求十進位的@value除以的餘數,找到餘數對應十六進位的值    SET @result = SUBSTRING(@seq, (@value%16)+1, 1)    WHILE @value > 0    BEGIN        SET @digit = SUBSTRING(@seq, ((@value/16)%16)+1, 1)        SET @value = @value/16        IF @value <> 0            SET @result = @digit + @result    END    RETURN @resultENDGO--測試SELECT dbo.BigInt2HexStr(2147483647)SELECT dbo.BigInt2HexStr(2147483648)

執行上面的SQL，返回的十六進位如Figure3所示：

（Figure3：資料對比）

要想理解上面的函數，你需要理解十進位轉換為十六進位的運算規則，假如十進位數23785轉為十六進位，計算的公式的步驟為：

1. 23785/16=1486餘9，十進位的9對應十六進位的9；
2. 1486/16=92餘14，十進位的14對應十六進位的E；
3. 92/16=5餘12，十進位的12對應十六進位的C；
4. 5/16=0餘5，十進位的5對應十六進位的5；

將餘數對應的十六進位倒寫，即5CE9，所以十進位23785 = 十六進位0x5CE9

 

三、十進位轉換為三十六進位

通過上面的例子修改下就能支援十進位到三十六進位的轉換了：

--十進位轉換為十六進位CREATE FUNCTION BigIntTo36HexStr(@value BIGINT)RETURNS VARCHAR(50)ASBEGIN    DECLARE @seq CHAR(36)    DECLARE @result VARCHAR(50)    DECLARE @digit CHAR(1)        SET @seq = ‘0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ‘    SET @result = SUBSTRING(@seq, (@value%36)+1, 1)    WHILE @value > 0    BEGIN        SET @digit = SUBSTRING(@seq, ((@value/36)%36)+1, 1)        SET @value = @value/36        IF @value <> 0             SET @result = @digit + @result    END     RETURN @resultENDGO--測試SELECT dbo.BigIntTo36HexStr(35)

執行上面的測試SQL，返回0xZ，測試成功；

 

四、補充說明

其實編寫Binary2HexStr函數是沒有必要的，因為SQL Server提供系統的函數支援轉換master.dbo.fn_varbintohexstr或者master.dbo.fn_varbintohexsubstring，例如：

SELECT master.dbo.fn_varbintohexstr(2147483647)SELECT master.dbo.fn_varbintohexsubstring(1,2147483647,1,0)

不過他們一樣存在對十進位資料的轉換不能超過2147483648的限制。

 

五、參考文獻

SqlServer中varbinary轉換成字串

關於遞增序列的問題

進位轉換

SQL之36進位轉換成10進位資料

SQL Server 中，實現 varbinary 與 varchar 類型之間的資料轉換

SQL Server: convert varbinary to varchar

SQL Server 進位轉換函式