Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

T-SQL Integer Data-Type Conversions

By Peter Kierstead,

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...

Total article views: 6874 | Views in the last 30 days: 7
 
Related Articles
FORUM

Convert alphanumeric to BigInt

Convert alphanumeric to BigInt

FORUM

convert DateTime to BigInt

convert DateTime to BigInt

FORUM

convert a datetime object into bigint

need to convert a datetime object into BIGINT

FORUM

How to Search Name string for space and convert lastname substring

Search Name string for space and convert lastname substring

FORUM

convert bigint to int

How to convert bigint to int

Tags
bigint    
hex    
int    
nvarchar    
string manipulation    
t-sql    
varchar    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones