How to convert from varbinary to char/varchar in mysql

Question

I have a field which is varbinary. It has already been populated. Now how do i convert varbinary to varchar so that I can use the data in the field for some other purpose. I use a MySQL version 5.10

score 45 · Answer 1 · answered Sep 22 '11 at 08:22

45

Late answer...

You can use CAST or CONVERT thus

CAST(foo AS CHAR(100))
CONVERT(foo, CHAR(100))

Supported types (5.5) are:

BINARY[(N)]
CHAR[(N)]
DATE
DATETIME
DECIMAL[(M[,D])]
SIGNED [INTEGER]
TIME
UNSIGNED [INTEGER]

You can not cast to varchar directly.
There is an open MySQL bug from 2008 which no-one seems to care about and is damn annoying

answered Sep 22 '11 at 08:22

gbn

422,506
82
585
676

1

@gbn, What's the default encoding used for the `CHAR` in `convert`? Also, now that Oracle has taken over MySQL, are people putting more attention into fixing the bug? – Pacerier Jan 31 '15 at 09:07
1

@Pacerier: sorry, I'm not active with MySQL nowadays, – gbn Feb 03 '15 at 12:57

score 28 · Answer 2 · answered Mar 08 '10 at 08:47

28

The MySQL syntax that worked for me in a similar scenario to this is:

select cast(binaryColumn as CHAR) from table_name

answered Mar 08 '10 at 08:47

yanigisawa

731
1
8
19

score 1 · Answer 3 · edited Sep 17 '20 at 01:00

1

You can use cast operation:

select cast(column_name as char)
  from table_name

edited Sep 17 '20 at 01:00

Andrew Brēza

7,705
3
34
40

answered Dec 09 '09 at 10:51

Pablo Santa Cruz

176,835
32
241
292

2

You cannot case to varchar as per the above comments. – Lukos Sep 25 '12 at 16:21
But you can use `char` as per other above comment. – Bramus Oct 16 '15 at 22:16

How to convert from varbinary to char/varchar in mysql

3 Answers3

Linked