Problem:

HEX function encodes a bytes or string into a hexadecimal encoding, but the result of the function is vary between dialects, due to the case of the result.

For example,
in bigquery (lowercase):

select to_hex(MD5('Hello'))
|  Row  |              f0_            |  
| ----- | --------------------------- |
|   1   | 8b1a9953c4611296a827abf8c47 |

in presto/trino (uppercase):

presto> select to_hex(to_utf8('some string'));
         _col0          
------------------------
 736F6D6520737472696E67 
(1 row)

in clickhouse return uppercase.
in spark (uppercase), but hex(md5_digest(x)) produce a lowercase result:

>>> spark.sql("select hex('Hello')").collect()
[Row(hex(Hello)='48656C6C6F')]
>>> spark.sql("select md5('Hello')").collect()
[Row(md5(Hello)='8b1a9953c4611296a827abf8c47804d7')]

In snowflake, the default behavior is uppercase, but we can pass an additional argument to control the case: https://docs.snowflake.com/en/sql-reference/functions/hex_encode#argumentshttps://docs.snowflake.com/en/sql-reference/functions/hex_encode#arguments

And so on...

=> So, as a transpiler tool, should we ensure the behavior of this function while transpiling?

The solution if needed:

We can add a new exp.UpperHex (or exp.LowerHex, depends on the case of default exp.Hex).
Also, we add a flag to Dialect class, which mark the behavior of HEX function of the target dialect is uppercase or lowercase, then can safety and can simplify the SQL while parsing or generating the sql.

For example, if read dialect is bigquery (lowercase), write dialect is trino (uppercase), the following step will happen when transpiling the sql select lower(to_hex(col_x)):

1. Parse sql to bigquery: becasue bq are already lowercased, the tree will look like: exp.SELECT (exp.Hex( col_x ))
2. Generate trino sql: because trino is uppercased, generator will be generating SELECT LOWER(TO_HEX(col_x))

If we transpile select upper(to_hex(col_x)) from bigquery to trino, the code will work like this:

1. parse sql to exp.Select( exp.UpperHex( col_x ))
2. Generate trinno sql: SELECT TO_HEX(col_x)

Describe alternatives you've considered
A clear and concise description of any alternative solutions or features you've considered.

Additional context
Add any other context or screenshots about the feature request here.