[SOLVED] trino md5 encode a varchar value and get output as string

trino md5 encode a varchar value and get output as string

I have a postgresql equivalent query:

>> select md5('hello')
5d41402abc4b2a76b9719d911017c592

>> select md5('hello')='5d41402abc4b2a76b9719d911017c592'
True

I am trying a similar query on trino engine.

>> select md5('hello')

Unexpected parameters (varchar(5)) for function md5. Expected: md5(varbinary) io.trino.spi.TrinoException: Unexpected parameters (varchar(5)) for function md5. Expected: md5(varbinary)

So I passed a varbinary value like:

>> select md5(cast('hello' as varbinary))
 OR
>> select md5(to_utf8('hello'))

5d41402a-bc4b-2a76-b971-9d911017c592 << ignore the hyphens for now
``

Now I'm trying to compare this encoded string with previous `md5` expression:

select md5(to_utf8('hello'))='5d41402a-bc4b-2a76-b971-9d911017c592'

Cannot apply operator: varbinary = varchar(36) io.trino.spi.TrinoException: Unexpected parameters (varbinary, varchar(36)) for function $operator$equal. Expected: $operator$equal(T, T) T:comparable


This again shows the operator error.

How do I get this value as a string?

I tried `select from_utf8(md5(to_utf8('hello')))` and it gives this weird output:

    ]A@*�K*v�q��Œ

Solution

I was able to test and found the string returned is base64 and you needed a hex value to compare.Try passing the value of md5() to to_hex() which will do the trick.

 select to_hex(md5(to_utf8('hello')))='5D41402ABC4B2A76B9719D911017C592'

output:

_col0
true