• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 486
  • Last Modified:

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

I have a varbinary field in SQL that stores dat like:
0x0001000000FFFFFFFF0100000000000000054F2F27ABCD712345677465737454F2F27ABCD712345677465737454F2F27ABCD712345677465737454F2F27ABCD712345677465737454F2F27ABCD712345677465737454F2F27ABCD712345677465737454F2F273206ABCDEF

How do I convert this to a field with readable words?
0
wademi
Asked:
wademi
1 Solution
 
Nico BontenbalCommented:
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
 
Brendt HessSenior DBACommented:
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
 
Alpesh PatelAssistant ConsultantCommented:
Select Convert(varbinary,'Alpesh')
Select Convert(varchar,0x416C70657368)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now