unsigned right shift '>>>' Operator in sql server
Solution 1
T-SQL has no bit-shift operators, so you'd have to implement one yourself. There's an implementation of a bitwise shifts here: http://dataeducation.com/bitmask-handling-part-4-left-shift-and-right-shift/
You'd have to cast your integer to a varbinary, use the bitwise shift function and cast back to integer and (hopefully) hey-presto! There's your result you're expecting.
Implementation and testing is left as an exercise for the reader...
Edit - To try to clarify what I have put in the comments below, executing this SQL will demonstrate the different results given by the various CASTs:
SELECT -5381 AS Signed_Integer,
cast(-5381 AS varbinary) AS Binary_Representation_of_Signed_Integer,
cast(cast(-5381 AS bigint) AS varbinary) AS Binary_Representation_of_Signed_Big_Integer,
cast(cast(-5381 AS varbinary) AS bigint) AS Signed_Integer_Transposed_onto_Big_Integer,
cast(cast(cast(-5381 AS varbinary) AS bigint) AS varbinary) AS Binary_Representation_of_Signed_Integer_Trasposed_onto_Big_Integer
Results:
Signed_Integer Binary_Representation_of_Signed_Integer Binary_Representation_of_Signed_Big_Integer Signed_Integer_Transposed_onto_Big_Integer Binary_Representation_of_Signed_Integer_Trasposed_onto_Big_Integer
-------------- -------------------------------------------------------------- -------------------------------------------------------------- ------------------------------------------ ------------------------------------------------------------------
-5381 0xFFFFEAFB 0xFFFFFFFFFFFFEAFB 4294961915 0x00000000FFFFEAFB
Solution 2
SQL Server does not support unsigned integers, so your value doesn't fit in the range of INT.
You can get a true binary interpretation by:
SELECT CAST(-5381 AS VARBINARY)
If you're happy to work within the confines of floating point arithmetic, you could do:
SELECT -5381.0 + (POWER(2, 30) * 4.0)
Musakkhir Sayyed
Apparently, this user prefers to keep an air of mystery about them.
Updated on June 04, 2022Comments
-
Musakkhir Sayyed over 1 year
How to write unsigned right shift operator in sql server? The expression is like
value >>> 0
Here is the e.g. -5381>>>0 = 4294961915
-
Ed B over 8 yearsHaving re-read your question, what I see you are ACTUALLY trying to do is cast a signed integer to an unsigned one (you're shifting by 0 bits). SQL Server doesn't have unsigned integers, but it DOES have 64-bit ones, and the bit pattern of a 32-bit signed integer transposed onto a 64-bit signed integer gives the same numeric value as the 32-bit unsigned integer you wanted. Try this:
select cast(cast(-5381 as varbinary) as bigint)
-
Ed B over 8 yearsNote that you can't just cast from
int
tobigint
, as this respects the twos complement, and if the number is negative, fills the high order 4 bytes with 1s; casting via a varbinary makes it 'forget' about the sign bit, and casting back to bigint assumes the high 4 order bytes are filled with 0s instead. Note that the result you are after (4294961915) is beyond the bounds of a signed 32 bit integer, so you will have to work with a 64-bit one instead! -
Reversed Engineer over 4 years"SQL Server does not support unsigned integers" - yes it does!! TINYINT datatype is an unsigned byte (0 to 255). None of the bigger integers though, so not very useful if you specifically want an unsigned one.