Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Getting the VARBINARY data into a VARCHAR field


Getting the VARBINARY data into a VARCHAR field

Author
Message
Danspojken
Danspojken
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 123
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!
Cadavre
Cadavre
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2596 Visits: 8437
DECLARE @test AS VARBINARY(MAX)
SET @test = 0x4858FE25053CA4D236F3D61AE29668BA

SELECT @test
,Substring(( MASTER.dbo.Fn_varbintohexstr(@test) ), 1, 2) +
Substring(Upper(MASTER.dbo.Fn_varbintohexstr(@test)), 3, 8000)




Forever trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


Craig Wilkinson - Software Engineer
LinkedIn
Danspojken
Danspojken
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 123
Ahhh... :-D Thanks a lot!
Paul White
Paul White
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10338 Visits: 11350
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
SQLblog.com
@SQL_Kiwi
BOR15K
BOR15K
SSC Veteran
SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)

Group: General Forum Members
Points: 200 Visits: 445
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?
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14965 Visits: 38985
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.


/*
NormalConvert BigEndian
$ 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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14965 Visits: 38985
i got it, it's the leading char:


/*
NormalConvert BigEndian FinalResults
$ 1 egaP thgilF rof eeF noitcasnarT Transaction Fee for Flight Page 1
* 1 egaP thgilF rof eeF noitcasnarT noddA Addon 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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

BOR15K
BOR15K
SSC Veteran
SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)

Group: General Forum Members
Points: 200 Visits: 445
Thank you, Lowell!

It did the trick!
frederico_fonseca
frederico_fonseca
SSC-Enthusiastic
SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)

Group: General Forum Members
Points: 139 Visits: 985
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


BOR15K
BOR15K
SSC Veteran
SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)

Group: General Forum Members
Points: 200 Visits: 445
Nice one, indeed!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search