SQL Server hexadecimal

In the project, you may have encountered a situation where you need to convert decimal to other hexadecimal formats, there are already many binary, octal, hexadecimal, and decimal conversion methods. But in some projects, these may not meet the requirements, and may require 17, 18, or even 32, 36 hexadecimal and decimal conversion. What should we do? It is not wise to write a function for every input. So here I provide a conversion function (n <= 32) between the decimal and the N-base ).

N-base function 1. Preparation

Before writing the n-base function, you need to have a base table for storing the n-base characters. Here I use a table function to represent it:

 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 @ 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 @ Temp Values  (  Char  ( @ I + 48 ), @ I + 48 )  End set @ I = @ I + 1End returnend 

2. decimal conversion to n-Base

 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. Convert n to decimal

 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 @ Result End 

Note: currently, the maximum hexadecimal value (36 hexadecimal value) can be 13 characters at most, but I think this is enough, because the range represented by the 36-digit system is much larger than the 13-digit 10-digit system, 0 <= Y <= 36*3612 + 36*3611 + ...... + 36*361 + 36. So the range that can be expressed in an N-base should be: 0 <= Y <= N * Nx + N * Nx-1 +... + N * N1 + N.

How to Use

So how should we use these functions? Here is an example of a table with an auto-incrementing 36 hexadecimal field.

First, create a table:

 
Create TableXavi.Tb_test(IDChar(10)Primary Key,AccountVarchar(20),[Name]Nvarchar(10))
 

Then create a trigger:

 Create trigger Xavi . Tr_testinsertOn Xavi . Tb_test Instead of insert asset nocount ondeclare @ 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 Xavi . Tb_test ( ID , Account , [Name] )  Select  Replicate  (  '0'  , 10 - Len  ( @ Nsystemchar ) + @ Nsystemchar , Account , [Name] From Inserted

Insert 100 data records into the table:

Declare@ IIntset@ I=1While(@ I<=100)Begin insertXavi.Tb_testValues(@ I, Left (Replace(Convert(Varchar(100),Newid()),'-',''),10), Left (Replace(Convert(Varchar(100),Newid()),'-',''),10))Set@ I=@ I+1End
 

Run the following command to view the data in the table:

From this result, we can see that the ID column is already in hexadecimal notation.

Extended usage

with this n-base function, we can use a smaller number of digits to indicate a larger range when producing some unique codes, such as order numbers.