Menu
I have some data in string format that represents binary data (e.g. '0x0002'). Is there some function or trick where I can convert these from literal strings to binary? That is, I want '0x0002' to become 0x0002, and SELECT CAST('0x0002' AS BINARY(20)) obviously won't do that. I did come up with some painfully slow process that involves building up SQL Statements and assigning them to a variable and executing that (e.g. "EXEC (@Query)"), but I'm looking for something where I don't have to do that.
If it helps, here is a sample table that you can test this on:
CREATE TABLE #T (BinaryString VARCHAR(100))
INSERT INTO #T VALUES('0x0000000000000000000000000000000000000002') -- Binary = the integer 2
INSERT INTO #T VALUES('0x000000000000000000000000000000000000007B') -- Binary = the integer 123
568
The idea is to first calculate the length of the string as n and then run a loop n times. In each iteration store ASCII value of character in variable val and then convert it into binary number and store result in array finally print the array in reverse order.
TO_NUMBER converts a string to a number of data type NUMERIC. TO_CHAR performs the reverse operation; it converts a number to a string. CAST and CONVERT can be used to convert a string to a number of any data type. For example, you can convert a string to a number of data type INTEGER.
A binary string is a sequence of bytes. Unlike a character string which usually contains text data, a binary string is used to hold non-traditional data such as pictures. The length of a binary string is the number of bytes in the sequence. A binary string has a CCSID of 65535.
Older code, SQL 7, sp_hexadecimal
SQL 2005 (+ 2000 maybe), master.dbo.fn_varbintohexstr
SQL 2008, native
61
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
