[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to convert a varbinary field to a regular text ins SQL

Posted on 2011-05-06
5
Medium Priority
?
478 Views
Last Modified: 2012-05-11
I have a varbinary field in SQL that stores dat like:
0x0001000000FFFFFFFF0100000000000000054F2F27ABCD712345677465737454F2F27ABCD712345677465737454F2F27ABCD712345677465737454F2F27ABCD712345677465737454F2F27ABCD712345677465737454F2F27ABCD712345677465737454F2F273206ABCDEF

How do I convert this to a field with readable words?
0
Comment
Question by:wademi
3 Comments
 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 35706824
This seems to work
declare @b binary(4), @str varchar(255)
select @b = 0x58B132
select CAST(@b as varchar(8000))

Open in new window

but not for your sample string.
0
 
LVL 32

Accepted Solution

by:
Brendt Hess earned 2000 total points
ID: 35707596
DECLARE @t TABLE (
  vb varbinary(MAX)
  )
INSERT INTO @t VALUES (0x0001000000FFFFFFFF0100000000000000054F2F27ABCD712345677465737454F2F27ABCD712345677465737454F2F27ABCD712345677465737454F2F27ABCD712345677465737454F2F27ABCD712345677465737454F2F27ABCD712345677465737454F2F273206ABCDEF)


SELECT *
FROM @t

SELECT CAST(vb AS varchar(MAX))
FROM @t

Now, to see words, you have to get past the NULL values embedded in the string, so:

SELECT REPLACE(CAST(vb AS varchar(MAX)), CHAR(0), '')
FROM @t

This returns data like:

ÿÿÿÿO/'«Íq#EgtestTòòz¼×4VwFW7EO/'«Íq#EgtestTòòz¼×4VwFW7EO/'«Íq#EgtestTòòz¼×4VwFW7EO/'2«Íï

Clearly, some cleanup and data extraction will be needed to use this data, but the words are visible.
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 35711202
Select Convert(varbinary,'Alpesh')
Select Convert(varchar,0x416C70657368)
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question