Varbinary to Varchar - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Varbinary to Varchar

Author

Topic

cstokes91
Yak Posting Veteran

72 Posts

Posted - 2013-07-09 : 12:38:48

Hey guys,

I am working with some data that is in the format varbinary(72). I am trying to convert that to a readable text but for some reason both cast and convert are not working.

Any ideas?

Here is some sample data and the codes I've tried.


select id, comment1, cast(comment1 as varchar(max)) as comment1varchar
from database..table
where comment1 <> ''

AND


select id, comment1, convert(varchar(5000),comment1, 0) as comment1varchar
from database..table
where comment1 <> ''

Here is one of varbinary entries:
0x2900544F204245205345525645442046524F4D20383A30302020412E4D2E20544F20393A303020502E4D2E

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-07-09 : 13:14:41

what do you mean by Not Working? is it throwing error? or ?

Cheers
MIK

cstokes91
Yak Posting Veteran

72 Posts

Posted - 2013-07-09 : 13:58:08

The return is either blank or gives one random symbol or letter.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-07-09 : 14:37:11

check with this ?

convert(varchar(5000),comment1, 1)

Cheers
MIK

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-10 : 03:37:05

Two first bytes are length information.

DECLARE	@Data VARBINARY(MAX) = 0x2900544F204245205345525645442046524F4D20383A30302020412E4D2E20544F20393A303020502E4D2E;
 
SELECT	CAST(SUBSTRING(@Data, 3, LEN(@Data)) AS VARCHAR(MAX))

N 56°04'39.26"
E 12°55'05.63"

cstokes91
Yak Posting Veteran

72 Posts

Posted - 2013-07-15 : 11:00:36

Sorry for the delayed response... THANK YOU SwePeso!! :D

That worked and can finally get it out.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources