June 30, 2010 at 12:34 am
Hi there!
I'm facing a problem. I have loads of data in a table that is converted using the HASHBYTES function. The data might look like this:
0x4858FE25053CA4D236F3D61AE29668BA
0x0CD845BD273E24B24AC3E55BAA4BFC1E
0x3F51B8F7BBDBA6FD9EB4D9BF57635B6E
0xD4DD43F5D4E564F7878F13D15C3A0328
This field is in VARBINARY.
Is there anyway to convert this field to a VARCHAR, or some kind of string, showing the data in the same way as above?
When I convert the data to VARCHAR I get this data, cause it's VARBINARY:
HXþ%<¤Ò6óÖâ–hº
ØE½'>$²JÃå[ªKü
?Q¸÷»Û¦ýž´Ù¿Wc[n
ÔÝCõÔåd÷‡Ñ\:(
But I want it to look exactly the same but in the datatype VARCHAR, i.e.:
0x4858FE25053CA4D236F3D61AE29668BA
0x0CD845BD273E24B24AC3E55BAA4BFC1E
0x3F51B8F7BBDBA6FD9EB4D9BF57635B6E
0xD4DD43F5D4E564F7878F13D15C3A0328
Is there any way to do this?
Thanks in advance!
June 30, 2010 at 2:38 am
DECLARE @test-2 AS VARBINARY(MAX)
SET @test-2 = 0x4858FE25053CA4D236F3D61AE29668BA
SELECT @test-2
,Substring(( MASTER.dbo.Fn_varbintohexstr(@test) ), 1, 2) +
Substring(Upper(MASTER.dbo.Fn_varbintohexstr(@test)), 3, 8000)
June 30, 2010 at 2:52 am
Ahhh... 😀 Thanks a lot!
June 30, 2010 at 3:29 am
There are two drawbacks to that function:
1. It is undocumented and therefore unsupported.
2. As with all scalar functions, it is invoked once per row. That makes it slow.
There are a number of alternatives in SQL Server 2005, but your best bet is to upgrade to 2008, where the CONVERT function has been extended to perform this conversion natively. See http://blogs.msdn.com/b/sqlprogrammability/archive/2008/10/31/sql-server-2008-new-binary-hex-string-conversion-functionality-can-dramatically-improve-related-query-performance-by-orders-of-magnitude.aspx
That link also contains a T-SQL scalar function that can be used with SQL Server 2005. Though still slow, it does not rely on undocumented system functions. Peter Larsson posted an interesting approach using XML (http://sqlblog.com/blogs/peter_larsson/archive/2010/01/27/convert-binary-value-to-string-value.aspx):
-- Prepare value
DECLARE @bin VARBINARY(MAX)
SET @bin = 0x5BAA61E4C9B93F3F0682250B6CF8331B7EE68FD8
-- Display the results
SELECT @bin AS OriginalValue,
CAST('' AS XML).value('xs:hexBinary(sql:variable("@bin"))', 'VARCHAR(MAX)') AS ConvertedString
Perhaps the best (and fastest) option in 2005 is to write a CLR function.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 13, 2016 at 10:50 am
Sorry to revive this topic, but I have a same issue but neither CONVERT (VARCHAR(MAX)... nor master.dbo.fn_varbintohexstr worked for me.
two examples I have in SQL:
0x24005472616E73616374696F6E2046656520666F7220466C6967687420506167652020202031
I know it means "Transaction Fee for Flight Page 1 "
0x2A004164646F6E205472616E73616374696F6E2046656520666F7220466C6967687420506167652020202031
meaning "Addon Transaction Fee for Flight Page 1 "
the translations are from an old DOS based program, which used to write into SQL in the past.
Can anyone advise what else I can use, please?
June 13, 2016 at 11:09 am
ok i'm getting close;
if i switch your values to BigEndian instead of Little, i'm getting your string, but backwards; i cannot seem to get it converted back to the last reverse that is needed.
/*
NormalConvertBigEndian
$1 egaP thgilF rof eeF noitcasnarT
*1 egaP thgilF rof eeF noitcasnarT noddA
*/
DECLARE @MyTable TABLE(id int identity(1,1) NOT NULL PRIMARY KEY,val varbinary(max))
INSERT INTO @MyTable(val)
SELECT 0x24005472616E73616374696F6E2046656520666F7220466C6967687420506167652020202031 UNION ALL
SELECT 0x2A004164646F6E205472616E73616374696F6E2046656520666F7220466C6967687420506167652020202031
SELECT CONVERT(VARCHAR(max),val) As NormalConvert,
CONVERT(VARCHAR(max),cast(reverse(val) as varbinary(max))) AS BigEndian
FROM @MyTable
Lowell
June 13, 2016 at 11:13 am
i got it, it's the leading char:
/*
NormalConvertBigEndianFinalResults
$1 egaP thgilF rof eeF noitcasnarTTransaction Fee for Flight Page 1
*1 egaP thgilF rof eeF noitcasnarT noddAAddon Transaction Fee for Flight Page 1
*/
DECLARE @MyTable TABLE(id int identity(1,1) NOT NULL PRIMARY KEY,val varbinary(max))
INSERT INTO @MyTable(val)
SELECT 0x24005472616E73616374696F6E2046656520666F7220466C6967687420506167652020202031 UNION ALL
SELECT 0x2A004164646F6E205472616E73616374696F6E2046656520666F7220466C6967687420506167652020202031
;with MyCTE
AS
(
SELECT CONVERT(VARCHAR(max),val) As NormalConvert,
CONVERT(VARCHAR(max),cast(reverse(val) as varbinary(max))) AS BigEndian
FROM @MyTable
)
SELECT *,REVERSE(LEFT(BigEndian,LEN(BigEndian) -2)) As FinalResults FROM MyCTE
Lowell
June 13, 2016 at 11:16 am
Thank you, Lowell!
It did the trick!
June 13, 2016 at 6:59 pm
no need for the reverse.
first byte is the size of the string
second byte is null - maybe on longer strings it gets populate
declare @MyTable table
(id int identity (1, 1) not null primary key
,val varbinary(max)
)
insert into @MyTable
(val
)
select 0x24005472616E73616374696F6E2046656520666F7220466C6967687420506167652020202031
union all
select 0x2A004164646F6E205472616E73616374696F6E2046656520666F7220466C6967687420506167652020202031
select convert(varchar(max), val) as NormalConvert -- gets obliterated as second byte is a null value
,substring(convert(varchar(max), val), 3, len(val) - 2) as NormalConvert
, len(val) - 2
, convert(int, substring(val,1,1))
from @MyTable
June 14, 2016 at 3:39 am
Nice one, indeed!
June 20, 2016 at 6:13 pm
Not sure is it OK to ignore the length value stored in 1st 2 bytes of the string.
This one puts the length value to use:
declare @MyTable table
(id int identity (1, 1) not null primary key
,val varbinary(max)
)
insert into @MyTable (val)
select 0x24005472616E73616374696F6E2046656520666F7220466C6967687420506167652020202031
union all
select 0x2A004164646F6E205472616E73616374696F6E2046656520666F7220466C6967687420506167652020202031
SELECT convert(varchar(max),
SUBSTRING(val, 3, -- Get length from 1st 2 bytes
CONVERT(SMALLINT,
CONVERT(BINARY(2), REVERSE(SUBSTRING(val, 1, 2)))
-- reversed order of bytes, then converted to smallint
)
)
)
FROM @MyTable
_____________
Code for TallyGenerator
June 21, 2016 at 8:59 am
[h3]select convert(varchar(max), val) as NormalConvert -- gets obliterated as second byte is NULL[/h3]
"Obliterated" ?
Isn't the 00 byte a terminator as in other languages ? So the conversion simply stops at the end of the "string".
(If so, why is a 42-byte info value preceded by a "NULL" ?)
By the way, good pointer on the first byte being the length. Forgot about that one.
June 21, 2016 at 11:33 am
j-1064772 (6/21/2016)
[h3]select convert(varchar(max), val) as NormalConvert -- gets obliterated as second byte is NULL[/h3]"Obliterated" ?
Isn't the 00 byte a terminator as in other languages ? So the conversion simply stops at the end of the "string".
(If so, why is a 42-byte info value preceded by a "NULL" ?)
By the way, good pointer on the first byte being the length. Forgot about that one.
clarification - conversion does not stop at that point - but SSMS will not show it and that is the meaning of my obliterated on the comment when selecting the whole value.
June 21, 2016 at 12:11 pm
Thanks for your explanation.
My curiousity was raised when I read about 00 being a "NULL" which scrapped whatever was following it.
But since in T-SQL '1' + NULL + 'whatever after' yields NULL instead of just '1' I went a bit farther.
CREATE TABLE [dbo].[Table_2](
[varbin] [varbinary](50) NULL,
[string] [varchar](50) NULL
) ON [PRIMARY]
INSERT INTO Table_2 (varbin, string)
SELECT
0x24005472616E73616374696F6E2046656520666F7220466C6967687420506167652020202031,
CONVERT(varchar(50), 0x24005472616E73616374696F6E2046656520666F7220466C6967687420506167652020202031)
UNION ALL
SELECT
0x2A004164646F6E205472616E73616374696F6E2046656520666F7220466C6967687420506167652020202031,
CONVERT(varchar(50), 0x2A004164646F6E205472616E73616374696F6E2046656520666F7220466C6967687420506167652020202031)
SELECT LEN(string) from Table_2
results in
38
44
So the CONVERT statement faithfully converts all the characters, even those after the 00 trouble-maker. And what actually is in the table is different than what the SSMS shows. Something to keep in mind when I am trying to debug a statement - do not accept at face value what SSMS is saying.
I also tried exporting the table to Excel - again via SSMS. The result is the same in Excel as what is shown by SSMS - only the first byte - the length is saved in Excel.
June 21, 2016 at 12:47 pm
that is the reason why whenever looking at this type of data I use a replace(field, char(0), ' ') to look at the contents - in addition to the convert(varbinary...0 ) as my background from mainframes/COBOL required reading hex codes
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply