SQL split bigint into two int

Question

I need to store the unsigned int equivlant of both halfs of a bigint string in two separate fields in a DB for compatibility with a 3rd part solution.

I've looked at the solution here, and it seems to work well except for the fact it often returns negative numbers. I assume this is a result of being unsigned data where SQL expected it to be signed, but I am speculating there...

SQL - safely downcast BIGINT to INT

DECLARE @BigintDataLimitIn BIGINT = 8589934590;
DECLARE @RightHalf INT, @LeftHalf INT;

WITH v AS (SELECT CAST(@BigintDataLimitIn AS varbinary) AS bin)
SELECT
  @LeftHalf = SUBSTRING(bin, 1, 4),
  @RightHalf  = SUBSTRING(bin, 5, 4)
FROM v;

SELECT @LeftHalf,  @RightHalf
----------------------------------------
(LeftHalf)  (RightHalf)
1           -2

So my question is why does the right half output sometimes return an incorrect negative value, and how do I solve that so it is always correct?

I see the same weird results for certain input numbers when doing this with .NET as well.

Dim original As Int64 = 8589934590
Dim rightHalfUnSigned As UInt32 = CType(original And &H7FFFFFFF, UInt32)

Console.writeline(rightHalfUnSigned)
Output: 2147483646

I don't understand these functions underlying mechanics well enough to know why this happens for the input number 8589934590, yet for 8589934595 it works perfectly.

Show us a data sample that is causing the problem, also a desired result from that sample would be great. — Jorge Campos, Oct 13 '15 at 16:43
Correct Output: BIGINT = 8589934595 LeftHalf RightHalf RawBinary 2 3 0x0000000200000003 Incorrect Output: BIGINT = 8589934590 LeftHalf RightHalf RawBinary 2 -2 0x00000001FFFFFFFE — Brian Higgins, Oct 13 '15 at 16:46

shawnt00 · Accepted Answer · 2015-10-13T17:04:31.093

1

SQL Server is treating and displaying your bytes as signed integers. The data in binary is still correct.

About half of the time those bytes representing the least significant bytes of your numbers will fall under the negative range of a four-byte signed int. You'd have the same scenario with negative values being split: sometimes the lower half will be positive.

You might think of it this way. After splitting into two values you've now got two sign bits (the leading bits) rather than just the one you started out with.

This might work to get back the unsigned value you expected to see:

cast(0x00000000 + cast(@RightHalf as binary(4)) as bigint)

It might be even easier to just say as below which will retain the bigint type:

@LeftHalf  = @BigintDataLimitIn / 4294967296
@RightHalf = @BigintDataLimitIn % 4294967296

edited Oct 13 '15 at 17:04

answered Oct 13 '15 at 16:43

shawnt00

16,443
3
17
22

so how do I get the split value into the correct format? – Brian Higgins Oct 13 '15 at 16:49
What is your goal? To see that they're correct? To export? Display on a report? – shawnt00 Oct 13 '15 at 16:50
cast(0x00000000 + cast(@RightHalf as binary(4)) as bigint) seems to do the trick on the SQL side, any chance you can do the same magic on the VB.NET code? – Brian Higgins Oct 13 '15 at 16:57
Can you elaborate on the bin % 4294967296? I don't understand how you suggest to integrate / implement that – Brian Higgins Oct 13 '15 at 17:01
Probably just pick from one of these: http://stackoverflow.com/questions/14708778/how-to-convert-unsigned-integer-to-signed-integer-without-overflowexception – shawnt00 Oct 13 '15 at 17:03
I should have said `@BigintDataLimitIn` instead of `bin`. – shawnt00 Oct 13 '15 at 17:04
I guess that link was going the opposite direction. I don't want to steer you wrong on the VB/.Net side of things. It has been too long since I wrote VB regularly. – shawnt00 Oct 13 '15 at 17:09

score 1 · Answer 2 · answered Oct 13 '15 at 17:11

Perfect, Works great, and fewer lines of code than original. Awesome answer.

DECLARE @BigintDataLimitIn BIGINT = 8589934590;
DECLARE @RightHalf INT, @LeftHalf INT;

WITH v AS (SELECT CAST(@BigintDataLimitIn AS varbinary) AS bin)
SELECT
  @LeftHalf = SUBSTRING(bin, 1, 4)
FROM v;

SELECT @LeftHalf as LeftHalf,  @BigintDataLimitIn % 4294967296 as RightHalf
-----------------------------
LeftHalf    RightHalf
1           4294967294

SQL split bigint into two int

2 Answers2