SQL Server Binary

In the project, you may have encountered, you need to convert the decimal to other binary, Google on a search, there are many 2 binary, 8 binary, 16 and decimal conversion method. But in some projects, these may not meet the requirements, may require 17, 18 or even 32, 36 decimal conversion, then what should we do? It is not advisable to write a function for each of the binaries. So here I provide a reciprocal function (n<=32) between the decimal and N binary.

N-binary function 1, preparing for work

Before writing the n-ary function, there is a need to store the underlying table representing the n-ary characters, which I use as a table function:

CREATE FUNCTION Xavi. fn_nsystemtable()RETURNS @tempTABLE(IDSMALLINT IDENTITY, [Char]CHAR(1), [Ascii]SMALLINT)Asbegin DECLARE @ignoreAsciiTABLE([Ascii]SMALLINT)DECLARE @iINT SET @i= 58While(@i<= 64)BEGIN INSERT into @ignoreAsciiVALUES(@i)SET @i= @i+ 1END SET @i= 0While) begin IF not EXISTS (select 1 from @ignoreAscii = @i + 48begin INSERT into @temp values char+ 48+ 48 end SET @i = @i + 1 END RETURNEND
                                                                        

2, decimal conversion to n binary

CREATE FUNCTION Xavi. Fn_decimaltonsystem(@bigIntBIGINT, @nTINYINT)RETURNS VARCHAR(100)As BEGIN Declare @resultVARCHAR(100), @modeInt, @remainderInt, @iRetCHAR(1)SELECT @mode= @bigInt, @result=‘‘While(1= 1)BEGIN IF(@bigInt= 0OR @n= 0OR @n= 1)BEGIN SET @result=CONVERT(VARCHAR(100), @bigInt)Break END IF (@mode = 0) begin break END SET @remainder = @mode % @n = @mode /@n = [Char] from Xavi.fn_nsystemtable () ns WHERE Ns.id + 1 set @result + @result end RETURN @result end          

3. Convert N into decimal

CREATE FUNCTION Xavi. fn_nsystemtodecimal(@nSysVARCHAR(100), @nTINYINT)RETURNS Bigintasbegin Declare @resultInt, @iPosInt, @iTmpint Select @result= 0, @iPos= 0While(@iPos<Len(@nSys))BEGIN SELECT @iTmp= NS. ID-1From Xavi. fn_nsystemtable() NSWHERE NS. [Char]=SUBSTRING(@nSys,len (@nSys )-@iPos ,1set @result = @result + @iTmp power castcast (@iPos as bigintset @iPos + 1 end RETURN @ Resultend           

Note: Currently tested for highest binary (36 binary), up to 13-bit, but I think this is enough, because 36 binary can represent a far greater range than the 10-decimal 13-digit number, 0<=y<=36 * 3612 + 36 * 3611 +......+ 36 * 361 + 36. So an n-binary can represent a range that is: 0<=y<=n * Nx + N * Nx-1 +......+ n * N1 + N.

How to use

So how do we use these functions, and here's an example of a table that has a self-increment of 36 fields.

First create a table:

CREATE TABLE Xavi. Tb_test(    CHAR(PRIMARY KEYVARCHARNVARCHAR(10 ))

Then create a trigger:

CREATE TRIGGER Xavi. Tr_testinsertOn Xavi. tb_testINSTEAD of INSERT ASSET NOCOUNT ondeclare @maxIDBIGINT, @nTINYINT, @nSystemCharVARCHAR(10)SET @n= 36SELECT @maxID=ISNULL(MAX(Xavi. fn_nsystemtodecimal(ID, @n)), 0)From Xavi. tb_testSET @nSystemChar= xavi.fn_decimaltonsystem (@ Maxid  @n insert into Xavi,[name]SELECT replicate ' 0 ' ,10 - (@nSystemChar )) + @ Nsystemchar              

Then insert 100 numbers of data into this table:

DECLARE @iIntset @i= 1While(@i<= 100)BEGIN INSERT into Xavi. tb_testVALUES(@i, Left (REPLACE(CONVERT(VARCHAR(100newid ()), , '  ' 10replaceCONVERT< Span style= "Color:gray;" > (varchar" newid"-' , "= @i end        

Execute the data in the table below to get the results as follows:

From this result it should be observed that the ID column is already a 36 binary representation.

Extended usage

With this n-ary function, we can then make some code, such as unique code, order number, and so on, to use fewer digits to represent a larger range.

A foundation--transformation into the system

Today, the first day of formal learning is the basic < conversion, the above diagram expresses the relationship between the various systems, but also express learning it learning methods < learning a modeling idea to form a process to deal with the systematic reflex of the system >

Today, the snail in my tree will begin to record everything I've been through at 1.1 points.

1 Purpose of study

Learning a modeling idea forms a conditioned reflex to deal with things.

2 Main contents

Introduction Decimal has 10 cardinality: 0-9, Binary has two cardinality: 0-1; octal has eight cardinality: 0-7; hex has 16 cardinality: 0-9;a (Ten)-F (15);

1) Decimal A to other binary B (A-to-B-binary)

Key points: Use a divided by B, with short division and take the remainder, until the quotient is 0, from the bottom to the remainder of the connection is B;

2) Binary---Decimal

Step 1 Write base 2 write exponent (right to left 0) 3 multiplication factor (right to left) 4 cumulative

Example 0,111 (binary) 7 (decimal)

1) 2 2 2 2

2) 2³+2²+2 +2º

3) 0*2³+1*2²+1*2 +1*2º

4) 0+4+2+1=7

In the same vein, other binary A to decimal can use the formula

3) Other conversion into the system

1 * binary to octal

From right to left, the three-digit group uses 421 (binary 111 to decimal as 4+2+1=7) for conversion;

* Binary to hexadecimal

From right to left, the four-digit group uses 8421 (binary 1111 to decimal as 8+4+2+1=15) for conversion;

28 binary to binary/16 binary to binary

Follow the octal/16 binary in each digit is replaced by binary;

The deep solution from the network kcal

Cross-conversion between binary, octal, hexadecimal, and decimal in C #

To start with a simple look, the conversion between C # itself and the X-binary is as follows:

Decimal to Binary Console.WriteLine (Convert.ToString (69, 2));

Decimal to Octal Console.WriteLine (Convert.ToString (69, 8));

Decimal to hexadecimal Console.WriteLine (Convert.ToString (69, 16));

Binary Turn Decimal Console.WriteLine (Convert.ToInt32 ("100111101″, 2)");

Octal decimal Console.WriteLine (Convert.ToInt32 ("76″, 8)");

Hexadecimal to decimal Console.WriteLine (Convert.ToInt32 ("FF", 16));

Hexadecimal Turn decimal:

int mytempr = Int. Parse ("EA", System.Globalization.NumberStyles.HexNumber);

Decimal Turn hex:

This.myResult.Text = mytempr.tostring ("X"); The C # language has a lot to learn, here we mainly introduce the C # implementation of the conversion hex, including the introduction of the hexadecimal enumeration value is hexnumber and so on.

C # Implementation Convert hex

Any data inside the computer is stored in binary, so the binary is not related to the storage of the data, only the input and output. So, for a binary conversion, we only care about the results in the string.

The ToString () method is mentioned in the 4th above to convert a numeric value to a string, but in a string, the result is displayed in decimal. Now let's add some arguments to it so that the C # implementation can be converted to hexadecimal-using the ToString (string) method.

Here you need a string type argument, which is the format specifier. The hexadecimal format specifier is "x" or "X", and the difference between the two format specifiers is mainly a-f six digits: "x" means a-f is denoted by lowercase letters, while "X" means a-f is denoted by large letters. The following example:

private　void　TestHex()

　{　

　 int　a　=　188;

　　 this.textBox1.Text　=　"";　

　 this.textBox1.AppendText("a(10)　=　"　+　a.ToString()　+　"\n");　

　 this.textBox1.AppendText("a(16)　=　"　+　a.ToString("x")　+　"\n");　

　 this.textBox1.AppendText("a(16)　=　"　+　a.ToString("X")　+　"\n");　

　 }　

The results of the operation are as follows:

a(10)　=　188　　

a(16)　=　bc　　

a(16)　=　BC　

At this time, we may have another requirement, that is, in order to show the results neatly, we need to control the length of the hexadecimal representation, if the length is not enough, with a leading 0 fill. To solve this problem, we just need to write the number on the length after the format specifier "x" or "X". For example, to limit the length of 4 characters, you can write "X4". In the example above, add a sentence:

this.textBox1.AppendText("a(16)　=　"　+　a.ToString("X4")　+　"\n");　

The result will be output a (+) = 00BC.

Now, let's talk about how to convert a string that represents a hexadecimal number to an integer type. This conversion also requires the use of the Parse () method. Here, I need the Parse (String,system.globalization.numberstyles) method. The first parameter is a string that represents a hexadecimal number, such as "AB", "20" (representing the decimal 32), and so on. The second parameter, System.Globalization.NumberStyles, is an enumeration type that is used to denote that the hexadecimal enumeration value is hexnumber. So, if we're going to convert "AB" to an integer, we should write this: int b = Int. Parse ("AB", System.Globalization.NumberStyles.HexNumber), the last obtained value of B is 171.

SQL Server Binary