The following short article presents a few T-SQL techniques that can be used to convert integer data from one format to another. The techniques are very simple and can be implemented in one or two lines of code.

T-SQL Int Conversions

Assume the following T-SQL preamble:

Declare @i Int,@s VarChar(max);
Set @i=16910859;

Converting a T-SQL Int into a string:

Print Cast(@i as VarChar);

Converting a T-SQL Int into hex:

Print Cast(@i as VarBinary(4));

Converting a T-SQL Int into a hex string:

Select
   @s=Cast(Cast(@i as VarBinary) as UniqueIdentifier),
   @s=SubString(@s,7,2)+SubString(@s,5,2)+SubString(@s,3,2)+SubString(@s,1,2);
Print @s;

T-SQL BigInt Conversions

Assume the following T-SQL preamble:

Declare @i BigInt,@s VarChar(max);
Set @i=72623859874597901;

Converting a T-SQL BigInt into a string:

Print Cast(@i as VarChar);

Converting a T-SQL BigInt into hex:

Print Cast(@i as VarBinary(8));

Converting a T-SQL BigInt into a hex string:

Select
   @s=Cast(Cast(@i as VarBinary) as UniqueIdentifier),
   @s=SubString(@s,7,2)+SubString(@s,5,2)+SubString(@s,3,2)+SubString(@s,1,2)+
      SubString(@s,12,2)+SubString(@s,10,2)+SubString(@s,17,2)+SubString(@s,15,2);
Print @s;

T-SQL String Conversions

Assume the following T-SQL preamble:

Declare @i Int,@s VarChar(max);
Set @s='16910859';

Convert a T-SQL integer string into Int:

Print Cast(@s as Int);

Convert a T-SQL integer string into hex:

Print Cast(Cast(@s as Int) as VarBinary(4));

Assume the following T-SQL preamble:

Declare @i BigInt,@s VarChar(max);
Set @s='72623859874597901';

Convert a T-SQL integer string into BigInt:

Print Cast(@s as BigInt);

Convert a T-SQL integer string into hex:

Print Cast(Cast(@s as BigInt) as VarBinary(8));

Assume the following T-SQL preamble:

Declare @i Int,@s nVarChar(max);
Set @s='01020A0B';

Convert a T-SQL hex string into Int:

Set @s='Set @i=0x'+@s;
Exec sp_ExecuteSQL @s,N'@i Int Output',@i Output;

Assume the following T-SQL preamble:

Declare @i BigInt,@s nVarChar(max);
Set @s='010203040A0B0C0D';

Convert a T-SQL hex string into Int:

Set @s='Set @i=0x'+@s;
Exec sp_ExecuteSQL @s,N'@i BigInt Output',@i Output;

Observations

Consider the following code:

Declare @s VarChar(max);
Set @s='This is a test of a string conversion.';
Print Cast(@s as VarBinary);
Print Cast(Cast(@s as VarBinary) as UniqueIdentifier);

The first Print statement outputs:

0x5468697320697320612074657374206F66206120737472696E6720636F6E

The second Print statement outputs:

73696854-6920-2073-6120-74657374206F

The first 16 characters are converted to their hex representations and deterministically distributed in the UniqueIdentifier output.

Try the same thing with an nVarChar string to see the differences and how they will effect your code.

Final Notes:

• T-SQL type Int is storage-equivalent to VarBinary(4).
• T-SQL type BigInt is storage-equivalent to VarBinary(8).
• Be aware of VarChar vs nVarChar issues.
• UniqueIdentifier auto-casts to a string type when used in string functions.
• UniqueIdentifer can used with an adaptation of the above techniques to convert any string data of any length to its corresponding hex values.
• sp_ExecuteSQL is your friend!
• Disclaimer: Provide your own data checking and/or error trapping. Your mileage may vary...