Convert a Binary Stored as Varchar to Binary

SQL Server : files as binary text extract

Cast the text column as varchar(MAX) and convert to varbinary(MAX) with binary style 2:

SELECT CONVERT(varbinary(MAX), CAST(YourTextColumn AS varchar(MAX)), 2)
FROM dbo.YourTable;

Storing binary data as nvarchar/varchar data in sql server

If you're trying to just store the 0x000... as a string without converting it to what the binary value actually represents, you can do that using CONVERT(0x000..., 1) though, for the specific value in your question, you need more than 60 characters.

DECLARE @tempBinayToNvarchar table (FinalValue nvarchar(120));

INSERT @tempBinayToNvarchar(FinalValue)
  SELECT CONVERT(nvarchar(120), 
    0x000000000000000000000000000000000000000000000000000000000000004, 
  1);
  
SELECT FinalValue FROM @tempBinayToNvarchar;

Output:

FinalValue
0x0000000000000000000000000000000000000000000000000000000000000004

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)

Unable to convert varchar to binary in case statement

The problem is your CASE expression and its return values. In the expression you have the 3 values that could be returned (which I've added their datatype to):

CONVERT(varbinary(MAX), T.N.value('DocContent[1]', 'varchar(max)')) --varbinary(MAX)
'' --varchar(1)
T.N.value('DocContent[1]', 'varchar(max)') --varchar(MAX)

For a CASE expression, the return datatype is what ever data type has the highest priority Data type precedence (Transact-SQL). varbinary has a priority of 29, and varchar 27. As varchar has a higher priority, a varchar is returned, and any values returned from the expression CONVERT(varbinary(MAX), T.N.value('DocContent[1]', 'varchar(max)')) will be implicitly cast to a varchar(MAX).

A CASE expression cannot return different data types (as above, if a WHEN/ELSE returns a data type different to a WHEN/ELSE in the same CASE expression with a lower precedence it'll be implicitly converted), however, perhaps you are after this?

   CONVERT(varbinary(MAX),CASE
                               WHEN T.N.value('DocContent[1]', 'varchar(max)') IS NOT NULL THEN T.N.value('DocContent[1]', 'varchar(max)')
                               WHEN T.N.value('DocContent[1]', 'varchar(max)') IS NULL THEN ''
                               ELSE T.N.value('DocContent[1]', 'varchar(max)')
                          END) AS [will Convert]

Edit: As @gotqn notes, the above can be shorted still. Firstly, there is no need for the ELSE here, as you can checking for if the same expression IS NULL or NOT NULL, therefore it's impossible for one of those expressions to not be true. Also, due to the way ISNULL works (it returns the datatype of the 1st parameter, it doesn't use Data Type Precedence), you don't need to explicitly CONVERT the latter values:

ISNULL(CONVERT(varbinary(MAX),T.N.value('DocContent[1]', 'varchar(max)')),'')

Convert binary to varchar

declare @val binary(20)
set @val=0x24000000008B0100000000

select @val, CONVERT(varchar(max),@val,1)

Convert non-hex text in varchar(max) to varbinary

When one wishes to convert data from one datatype to another, and no implicit convert exists, one uses either CAST or CONVERT.

e.g.

select cast(MyColumn as varbinary(max)), convert(varbinary(max), MyColumn)
from MyTable;

CAST is ANSI-SQL for what it is worth whereas CONVERT is SQL Server specific. However CONVERT handles many other cases including specific formatting, which CAST doesn't handle.

OK, taking a total guess here, many people encode binary data as base64 so try this:

SELECT CAST(CAST(N'' AS XML).value('xs:base64Binary(sql:column("MyColumn"))', 'VARBINARY(MAX)') AS VARCHAR(MAX))
FROM MyTable;