Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

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, December 21, 2015 12:02 AM
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!
Post #945227
Posted Wednesday, June 30, 2010 2:38 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 5:28 AM
Points: 2,491, Visits: 8,364
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
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, December 21, 2015 12:02 AM
Points: 8, Visits: 123
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, July 22, 2016 6:18 PM
Points: 9,932, Visits: 11,342
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
Post #945320
Posted Monday, June 13, 2016 10:50 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, June 28, 2016 8:55 AM
Points: 164, Visits: 396
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?
Post #1794232
Posted Monday, June 13, 2016 11:09 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:22 AM
Points: 14,445, Visits: 37,857
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!
Post #1794239
Posted Monday, June 13, 2016 11:13 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:22 AM
Points: 14,445, Visits: 37,857
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!
Post #1794241
Posted Monday, June 13, 2016 11:16 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, June 28, 2016 8:55 AM
Points: 164, Visits: 396
Thank you, Lowell!

It did the trick!
Post #1794242
Posted Monday, June 13, 2016 6:59 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 9:10 AM
Points: 69, Visits: 578
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

Post #1794327
Posted Tuesday, June 14, 2016 3:39 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, June 28, 2016 8:55 AM
Points: 164, Visits: 396
Nice one, indeed!
Post #1794399
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse