In SQL Server, How to Convert Binary Strings to Binary

Convert a BINARY stored as VARCHAR to BINARY

The result you get is because the string "0003f80075177fe6" (a VARCHAR value) is converted to code points, and these code points are served up as a binary value. Since you're probably using an ASCII-compatible collation, that means you get the ASCII code points: 0 is 48 (30 hex), f is 102 (66 hex) and so on. This explains the 30 30 30 33 66 38 30 30...

What you want to do instead is parse the string as a hexadecimal representation of the bytes (00 03 f8 00 75 71 77 fe 66). CONVERT accepts an extra "style" parameter that allows you to convert hexstrings:

SELECT CONVERT(BINARY(16), '0003f80075177fe6', 2)

Style 2 converts a hexstring to binary. (Style 1 does the same for strings that start with "0x", which is not the case here.)

Note that if there are less than 16 bytes (as in this case), the value is right-padded with zeroes (0x0003F80075177FE60000000000000000). If you need it left-padded instead, you have to do that yourself:

SELECT CONVERT(BINARY(16), RIGHT(REPLICATE('00', 16) + '0003f80075177fe6', 32), 2)

Finally, note that binary literals can be specified without conversion simply by prefixing them with "0x" and not using quotes: SELECT 0x0003f80075177fe6 will return a column of type BINARY(8). Not relevant for this query, but just for completeness.

SQL Server Convert integer to binary string

Following could be coded into a function. You would need to trim off leading zeros to meet requirements of your question.

declare @intvalue int
set @intvalue=5

declare @vsresult varchar(64)
declare @inti int
select @inti = 64, @vsresult = ''
while @inti>0
  begin
    select @vsresult=convert(char(1), @intvalue % 2)+@vsresult
    select @intvalue = convert(int, (@intvalue / 2)), @inti=@inti-1
  end
select @vsresult

In Sql Server, how to convert binary strings to binary?

Older code, SQL 7, sp_hexadecimal

SQL 2005 (+ 2000 maybe), master.dbo.fn_varbintohexstr

SQL 2008, native

How do you set a binary value from a string variable comprised of a binary literal in SQL Server?

You can convert your varbinary to varchar, and join on it (or relate it, as you stated).

declare @v varbinary(16) = 0x2F774578C33011E880D80050569C29CA
select @v, convert(varchar(256), @v,2)

declare @s varchar(256) = '2F774578C33011E880D80050569C29CA'
select @s, convert(varbinary(16),@s,2)

So, for you:

 DECLARE @convo varchar(max) 
 SET @convo = (SELECT TOP (1)
 convert(varchar(256),[BinaryJobID],2)
 FROM [GAPClaims].[dbo].[Record2]
 WHERE binaryjobId IS NOT NULL ) 

See the Binary section in the docs for why I used 2 in the convert statement.

Convert integer into binary format in SQL Server

Here is a fancy way of doing it without using a function:

;WITH N(N)AS 
(SELECT 1 FROM(VALUES(1),(1),(1),(1),(1))M(N)),
tally(N)AS(SELECT ROW_NUMBER()OVER(ORDER BY N.N)FROM N, N a)

SELECT yourtable.num, x.bin
FROM (values(0),(1),(2),(3),(4),(9),(20),(100),(1001)) yourtable(num)
CROSS APPLY(
SELECT  
  REVERSE(( 
        SELECT cast(num / power(2, N - 1) % 2 as char(1))
        FROM tally t1 
        WHERE num >= power(2, N - 1)

        for xml path(''), type 
    ).value('.', 'varchar(max)')) [bin]
) x

Result:

num   bin
0     NULL
1     1
2     10
3     11
4     100
9     1001
20    10100
100   1100100
1001  1111101001

How can I convert a C# byte array into a binary string to be pasted in a SQL script?

Binary constants in SQL Server 'have the prefix 0x and are a string of hexadecimal numbers'.

You can convert each byte to hexadecimal using the "x2" format string and combine the result with String.Join():

var bytes = new byte[] {1, 2, 3, 4, 5, 6};
var binaryString = "0x" + string.Join("", bytes.Select(b => b.ToString("x2")));

Convert Binary Hex representation to string keeping same output

convert to a string then use substring to remove the 0x. convert has a special style for converting binary data to a string represented in hex.

substring(convert(varchar(12),CONVERT(BINARY(6), RW + CONVERT(INTEGER, @StartRange)),2/*Binary convert style*/),3,10)

Convert binary to varchar

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

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

---

Related Topics