Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Getting the VARBINARY data into a VARCHAR field Expand / Collapse
Author
Message
Posted Wednesday, June 30, 2010 12:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 24, 2013 2:07 AM
Points: 8, Visits: 116
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!
Post #945227
Posted Wednesday, June 30, 2010 2:38 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:56 AM
Points: 2,372, Visits: 7,560
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)




Not a DBA, just 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


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #945297
Posted Wednesday, June 30, 2010 2:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 24, 2013 2:07 AM
Points: 8, Visits: 116
Ahhh... Thanks a lot!
Post #945302
Posted Wednesday, June 30, 2010 3:29 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, September 19, 2014 12:48 PM
Points: 9,923, Visits: 11,169
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #945320
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse