CODEWITHSUNDEEP
mysqlsql-server
Jez
Jez

Reputation: 2444

MySQL equivalent of MSSQL CONVERT()

I have this fragment of MSSQL -

CONVERT(INT, HASHBYTES('MD5', {some_field}))

...and I'd really like a MySQL equivalent. I'm pretty sure the HASHBYTES('MD5', ...) bit is the same as MySQL's MD5(...) - it's the CONVERT(INT, ...) bit that's really puzzling me.

Thanks.

Upvotes: 5

Views: 1517

Answers (1)

eggyal
eggyal

Reputation: 125925

From the MySQL manual entry for the MD5() function:

The value is returned as a string of 32 hex digits, or NULL if the argument was NULL.

The MSSQL CONVERT() function which you quote above converts its varbinary argument to a signed 32-bit integer by truncating to the 4 lowest-order bytes. This is a bit of a nuisance because MySQL arithmetic works to 64-bit precision.

We must therefore take the rightmost 8 digits of MySQL's hex representation (representing the 4 lowest-order bytes) and convert to decimal using MySQL's CONV() function, then sign-extend the result:

CONV(RIGHT(MD5('foo'),8), 16, 10) ^ 0x80000000 - 0x80000000

Upvotes: 6

Related Questions