I am attempting to hash a string to a 64-bit value (bigint) in MySQL. I am aware of the MD5() function, which returns a 128-bit hash as a binary string. I'd be happy to just take the bottom or top 64 bits of this result. However, I cannot figure out how to get from a binary string type to a numeric type of any sort. Any pointers?
Add a comment
|
3 Answers
Use the CONV() function to convert the MD5 hash from base 16 to base 10 and CAST to convert it to a number:
select cast(conv(substring(md5(id), 1, 16), 16, 10) as unsigned integer) from SomeTable;
2 Comments
Sean Owen
Nice, that's what I'm looking for. I think I don't need the cast, or want to cast to bigint, but the conv() function was really what I was missing.
Sean Owen
One final note. I was then accessing this numeric value as a long in Java. Java integer types are signed, and the result of conv() is always positive, which means it overflows in some cases. For those using this as a signed long, you do need the cast, and casting to 'signed' does the trick.
You could also use CRC32 function that returns 32-bit unsigned value.
SELECT CRC32(id) from SomeTable;
Comments
CREATE FUNCTION dbo.HexStrToVarBinary(@hexstr varchar(8000))
RETURNS varbinary(8000)
AS
BEGIN
DECLARE @hex char(1), @i int, @place bigint, @a bigint
SET @i = LEN(@hexstr)
set @place = convert(bigint,1)
SET @a = convert(bigint, 0)
WHILE (@i > 0 AND (substring(@hexstr, @i, 1) like '[0-9A-Fa-f]'))
BEGIN
SET @hex = SUBSTRING(@hexstr, @i, 1)
SET @a = @a +
convert(bigint, CASE WHEN @hex LIKE '[0-9]'
THEN CAST(@hex as int)
ELSE CAST(ASCII(UPPER(@hex))-55 as int) end * @place)
set @place = @place * convert(bigint,16)
SET @i = @i - 1
END
RETURN convert(varbinary(8000),@a)
END
GO
2 Comments
Sean Owen
Nice! was hoping there was something built in.
Mike de Klerk
Now the other way around please :D :@ :)