Presto: cast varbinary field as varchar

[Liorba]

Hey
it seems that converting varchar to varbinary works fine
select cast('hello' as varbinary)
results as 68 65 6c 6c 6f
but when I tried to to is the opposite way
select cast('68 65 6c 6c 6f' as varchar)
i didn't get the word 'hello'

[nezihyigitbasi]

@Liorba since '68 65 6c 6c 6f' is a varchar literal, select cast('68 65 6c 6c 6f' as varchar) should return 68 65 6c 6c 6f.

And even if you get your query right, unfortunately currently varbinary to varchar cast is not supported in Presto.

presto:nyigitbasi> select cast(cast('hello' as varbinary) as varchar);
Query 20151205_001624_00004_jvscq failed: line 1:8: Cannot cast varbinary to varchar

[martint]

Also, 68 65 6c 6c 6f is just the way the CLI renders the varbinary, but internally it's just a bunch of bytes. There's no expectation that the display text can be converted back to a varbinary by casting it.

There's a pending PR to add support for binary literals, which will allow you to do this to produce a varbinary:

SELECT X'68 65 6c 6c 6f'

See https://github.com/facebook/presto/issues/3901 and https://github.com/facebook/presto/pull/3997 for more details

[Liorba]

so how can I use json function on data that is stored as varbinary but is actually a JSON?

[martint]

You can call json_parse(from_utf8(x)) to convert the varbinary to json.

See:
https://prestodb.io/docs/current/functions/string.html#from_utf8
https://prestodb.io/docs/current/functions/json.html#json_parse

[dain]

The reason we don't have a cast from varbinary to varchar is we don't know the character encoding of the binary data. For the opposite cast, we could just choose a single encoding.

[guyco33]

thanks @martint for the tip