CODEWITHSUNDEEP
mysqlmariadbhexbit-manipulation
Root0x
Root0x

Reputation: 490

MariaDB Bitwise AND Of Hex String Literal

I want to and mask the result of mariadb MD5 method to return me only the first 32 bits of the 128bit value.

My issue is that the result of the MD5 function is a varchar and I cannot and it unless its a integer.

I have tried to use the CONV function but that returns only as a 64 bit number so all my results are INT_MAX, an example of that query is.

SELECT MD5(A.Country), CONV(MD5(A.Country),16,10), A.Country
 FROM Analytics A

Which returns

+--------------------------------+--------------------------+-------+
|MD5(A.Country)                  |CONV(MD5(A.Country),16,10)|Country|
+--------------------------------+--------------------------+-------+
|8f14e45fceea167a5a36dedd4bea2543|18446744073709551615      |7      |
|c81e728d9d4c2f636f067f89cc14862c|18446744073709551615      |2      |
|c81e728d9d4c2f636f067f89cc14862c|18446744073709551615      |2      |
|c81e728d9d4c2f636f067f89cc14862c|18446744073709551615      |2      |
|c81e728d9d4c2f636f067f89cc14862c|18446744073709551615      |2      |
+--------------------------------+--------------------------+-------+

What is the correct way to convert this varchar into a integer so I can then bitwise and it or is there a way to forego the integer conversion entirely and bitwise and the varchar instead.

Upvotes: 0

Views: 251

Answers (2)

Rick James
Rick James

Reputation: 142296

select CONV(LEFT(MD5(2233), 8), 16,10);

Will give you, effectively, and INT UNSIGNED.

MD5() give you 32 hex digits.
LEFT(..., 8) gives you the first 8; you could pick some other subset.

Upvotes: 0

trincot
trincot

Reputation: 350310

or is there a way to forego the integer conversion entirely and bitwise and the varchar instead.

Yes: 32 bits is 8 hex characters, so you can do

select left(md5(a.country), 8)
from analytics a

Upvotes: 1

Related Questions