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

Posted on 2011-05-06
Last Modified: 2012-05-11
I have a varbinary field in SQL that stores dat like:

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

    Expert Comment

    by:Nico Bontenbal
    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.
    LVL 32

    Accepted Solution

      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:


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

    Expert Comment

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

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    755 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

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now