How to Convert from Varbinary to Char/Varchar in MySQL

varbinary to string on SQL Server

"Converting a varbinary to a varchar" can mean different things.

If the varbinary is the binary representation of a string in SQL Server (for example returned by casting to varbinary directly or from the DecryptByPassPhrase or DECOMPRESS functions) you can just CAST it

declare @b varbinary(max)
set @b = 0x5468697320697320612074657374

select cast(@b as varchar(max)) /*Returns "This is a test"*/

This is the equivalent of using CONVERT with a style parameter of 0.

CONVERT(varchar(max), @b, 0)

Other style parameters are available with CONVERT for different requirements as noted in other answers.

converting varchar to binary in mysql?

Since the accepted answer is unnecessarily complex, here a concise answer:

TLDR;

The result can be achieved in one step:

SELECT CONV(TRIM("'" FROM SUBSTRING("0b'1011'", 3)), 2, 16);

Explanation

Starting point is a string containing 0b1011:

SET @string = "0b'1011'"; -- (string) 0b'1011'

We can apply string operations to get it closer to a number:

SET @plainnumber = TRIM("'" FROM SUBSTRING(@string, 3)); -- (string) 1011

We can then convert the string into a real number, without sign because there is none: (You cannot use binary as a type here, because it is a string type. This step is optional because MySQL will cast implicitly.)

SET @number = CAST(@plainnumber AS UNSIGNED); -- (unsigned) 1011

You can now do whatever you want with it. The OP wanted to get the decimal representation which can be acquired with CONV:

SELECT CONV(@number, 2, 10);

Explanation for hexadecimal

If your starting point is a string containing a hexadecimal representation:

SET @string = "41"; -- 0x41

You can use the UNHEX function to get a binary string:

SELECT UNHEX(@string);

Or you can use CONV to get a numerical representation:

SELECT CONV(@string, 16, 10); -- decimal (65)
SELECT CONV(@string, 16, 2); -- binary (0100 0001)

Cannot cast varbinary to varchar in presto

Based on exception - msg column is not a varbinary but rather a ROW with one varbinary field named row1. You can access it with field reference operator . via name (msg.row1):

-- sample data
WITH dataset (msg, id) AS (
    VALUES (cast(row (to_utf8(cast(now() as varchar))) as row(row1 varbinary)), 1)
)

-- query
select from_utf8(msg.row1) 
from dataset

Output:

_col0
2022-05-14 14:09:30.391 UTC

SQL CONVERT from varbinary to varchar

Are you using SQL Server? If so, see this page for the SQL data type to CLR data type mappings: http://msdn.microsoft.com/en-us/library/cc716729.aspx

SQL Server char, varchar, nchar and nvarchar all map to/from a C# string (though a char[] will work as well).

SQL Server binary andvarbinarymap to/from a C#byte[]`.

What's the actual problem you're having?

Further, if you're passing binary data as a varchar to SQL Server, I would expect it to get munged in the tranformation between UTF-16 (CLR internal string encoding) to whatever code page SQL Server is using.

Another thing to note: your stored procedure:

ALTER PROCEDURE insertPlayerImage 
  @playerID varchar(9), 
  @profileImage varchar(max), 
  @pending char(1)
AS

  CONVERT(varbinary(max), @profileImage)

  INSERT INTO PlayerImage
  ( playerID , profileImage , pending )
  VALUES
  ( @playerID , @profileImage , @pending )

GO

isn't legal SQL. Convert() is a function, not a SQL statement. It doesn't even compile. If you are trying to convert your varchar parameter @profileImage to varbinary, you're going to have to do something along the lines of

 declare @image varbinary(max)
 set @image = convert(varbinary(max),@profileImage)

If you're stored procedure has the signature

create procedure dbo.insertPlayerImage

  @playerId     varchar(9) ,
  @profileImage varbinary(max) ,
  @pending      char(1)

as
...

Then this code will do you:

public int insertProfileImage( string playerId , byte[] profileImage , bool pending )
{
  if ( string.IsNullOrWhiteSpace(playerId) ) throw new ArgumentException("playerId" ) ;
  if ( profileImage == null || profileImage.Length < 1 ) throw new ArgumentException("profileImage") ;

  int rowCount ;

  string connectString = GetConnectString() ;
  using ( SqlConnection connection = new SqlConnection(connectString) )
  using ( SqlCommand command = connection.CreateCommand() )
  {

    command.CommandType = CommandType.StoredProcedure ;
    command.CommandText = "dbo.insertPlayerImage" ;

    command.Parameters.AddWithValue( "@playerId"     , playerId            ) ;
    command.Parameters.AddWithValue( "@profileImage" , profileImage        ) ;
    command.Parameters.AddWithValue( "@pending"      , pending ? "Y" : "N" ) ;

    rowCount = command.ExecuteNonQuery() ;

  }

  return rowCount ;
}

If however, you're passing a null for image data, you'll need to change how the value of the parameter gets set. Something along the lines of:

command.Parameters.AddWithValue( "@profileImage" , profileImage != null ? (object)profileImage : (object)DBNull.Value ) ;

SqlParameter p = new SqlParameter( "@profileImage" , SqlDbType.VarBinary ) ;
p.Value = DBNull.Value ;
if ( profileImage != null )
{
  p.Value = profileImage ;
}
command.Parameters.Add( p ) ;

Varbinary data INTO text and vice versa

Usually this can be done using CONVERT when you know the original chartset:

mysql> SELECT
    ->   CONVERT(x'ece0ece020ecfbebe020f0e0ecf320202020202020202020'
    ->           USING cp1251) as s;
+--------------------------------------+
| s                                    |
+--------------------------------------+
| мама мыла раму                       |
+--------------------------------------+
1 row in set (0.01 sec)

But I failed to find a chartset to decode bfebb1e2c0c720b8c1c5e420202020202020202020202020 to anything human readable.

SQL Server converting varbinary to string

Try:

DECLARE @varbinaryField varbinary(max);
SET @varbinaryField = 0x21232F297A57A5A743894A0E4A801FC3;

SELECT CONVERT(varchar(max),@varbinaryField,2), 
@varbinaryField

UPDATED:
For SQL Server 2008

What is the default charset for MySQL varbinary string interpretaion?

VARBINARY has no CHARACTER SET. All comparisons are done bit-by-bit. Such comparisons are somewhat like COLLATE ..._bin.

For most applications it is best to do everything in CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci.

The only reason I can think of for convert(myvarbinary using utf8) is if you have various encodings in a single column. That sounds like a nightmare.