Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

I want to take the first four bytes of a BINARY string and interpret it as numeric.

(Background: I have a SHA1 hash that has been UNHEX'ed into a BINARY(20) column, and I want to create a PARTITION BY HASH expression that takes the first four bytes of the SHA1 value and casts or converts it to a numeric value, as the PARTITION BY HASH expression is expected to.)

It really needn't be four bytes; just a short run of a few bytes that can be manipulated into being seen as an integer value.

This would convert the binary string to hex, take the first 8 hex characters from the converted string and convert that to decimal.

We take the first 8 hex characters as two hex characters are needed to represent one byte.

Converts numbers between different number bases. Returns a string representation of the number N, converted from base from_base to base to_base.

If you need to get the 4 first digits of 10011001 which is 1001 = 9 use:

SELECT CONV(SUBSTR(10011001, 1, 4), 2, 10)
if you have leading zeros you can use the LPAD function, for example the first 4 digits of  the binary value 01001100 which is 0100 = 4, you can do:
SELECT CONV(
  SUBSTR(
    LPAD(
      CAST(01001100 as char), (LENGTH(CAST(01001100 as char)) + 1), '0'), 
    1, 4), 2, 10)
See SQLFiddle
                Close.  I think CONV wants N to be an ASCII representation.  As I mentioned above, I'm working with a true binary "string."  Here's what I tried:  select conv(substr(unhex(sha1('a')),1,1),2,10); I'll keep fiddling.
– Chap
                Mar 5, 2013 at 15:26
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.