Converting/concatenating individual bytes from a VARCHAR into a BIGINT using T-SQL

Question

Working between two database systems, I need to convert a VARCHAR identifier into a BIGINT. My VARCHAR is always of the form "###-XXX-XXX-###" where # is any digit, and X is any alpha-numeric character (e.g. "103-AF7-GGB-005"). The two ### entries are guaranteed to be below 256, so I want to store each of those in a single byte of the BIGINT (the first and last bytes respectively), with each of the 6 other characters stored as a byte each.

I've implemented this outside of the database, and it solves the problem I needed it to, but now I have the need to create the function in t-sql and I have not been able to figure out how to do this.

Thanks in advance for the help!

Answer 1

You could do this:

DECLARE @inp VARCHAR(100) = '223-ABC-DEF-234'
    , @BITS BIGINT;

SELECT @BITS = 
        CASE 
            WHEN CONVERT(BIGINT, LEFT(@inp, 3)) > 127
                THEN (CONVERT(BIGINT, LEFT(@inp, 3))-128) * POWER(CONVERT(BIGINT, 2), 56) 
                     -9223372036854775808
            ELSE CONVERT(BIGINT, LEFT(@inp, 3)) * POWER(CONVERT(BIGINT, 2), 56)
        END
    + CONVERT(BIGINT, ASCII(substring(@inp, 5, 1))) * POWER(CONVERT(BIGINT, 2), 48)
    + CONVERT(BIGINT, ASCII(substring(@inp, 6, 1))) * POWER(CONVERT(BIGINT, 2), 40)
    + CONVERT(BIGINT, ASCII(substring(@inp, 7, 1))) * POWER(CONVERT(BIGINT, 2), 32)
    + CONVERT(BIGINT, ASCII(substring(@inp, 9, 1))) * POWER(CONVERT(BIGINT, 2), 24)
    + CONVERT(BIGINT, ASCII(substring(@inp, 10, 1))) * POWER(CONVERT(BIGINT, 2), 16)
    + CONVERT(BIGINT, ASCII(substring(@inp, 11, 1))) * POWER(CONVERT(BIGINT, 2), 8)
    + CONVERT(BIGINT, RIGHT(@INP, 3));

select CONVERT(binary(8), @bits);
-- Returns 0xDF414243444546EA

SELECT CONVERT(VARCHAR, CONVERT(INT, 0XDF))
    + '-' + CHAR(0X41)
    + CHAR(0X42)
    + CHAR(0X43)
    + '-' + CHAR(0X44)
    + CHAR(0X45)
    + CHAR(0X46)
    + '-' + CONVERT(VARCHAR, CONVERT(INT, 0XEA));

-- Returns 223-ABC-DEF-234: our original string

The big subtraction is to flip the sign bit. You're using all 64 bits if the first number is more than 127. Just multiplying by 2^56 will overflow the data type, since BIGINT is signed.