none
Remove 0x from binary values using t-sql RRS feed

  • Question

  • Hi All,

    How do I remove 0x from a binary value using t-sql.

    Input is 0x000000000000048B

    I need output to be 000000000000048B

    Please can anybody send me the t-sql function to convert like this.

    Thanks,

    RH

     


    sql
    Tuesday, November 29, 2011 4:40 PM

All replies

  • Check out the following solution:

    DECLARE @hex varchar(max)=
        master.dbo.fn_varbintohexstr(convert(varbinary(max), 0x000000000000048B))
    SELECT RIGHT(@hex,len(@hex)-2)
    -- 000000000000048b
    

     The function in the solution is undocumented.

    Related article:

    http://blogs.msdn.com/b/sqltips/archive/2008/07/02/converting-from-hex-string-to-varbinary-and-vice-versa.aspx


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Tuesday, November 29, 2011 4:47 PM
    Moderator
  • declare @k binary(8)
    
    set @k = 0x000000000000048B
    
    select convert(varchar, @k, 2)
    

    Tuesday, November 29, 2011 4:58 PM
  • And here i thought CONVERT() was only for dates.

    It's amazing what you can learn when re-reading the documentation.

    Tuesday, November 29, 2011 5:52 PM
    Moderator
  • Brian,

    The CONVERT solution is not robust:

    declare @k binary(20)
    set @k = 0x112233445566778899aabbccddeeff11223344
    select convert(varchar, @k, 2)
    -- 112233445566778899AABBCCDDEEFF
    


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Sunday, December 4, 2011 10:04 PM
    Moderator
  • Brian,

    The CONVERT solution is not robust:

     

    declare @k binary(20)
    set @k = 0x112233445566778899aabbccddeeff11223344
    select convert(varchar, @k, 2)
    -- 112233445566778899AABBCCDDEEFF
    

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM


    HI SQLUSA !

    Your observations are not correct, i did lil more reseaarch on this and here are my findings;

    DECLARE @myvarchar VARCHAR(MAX),@myvarbinary VARBINARY(MAX)
    SET @myvarchar = '112233445566778899aabbccddeeff11223344'
    SET @myvarbinary = CAST(@myvarchar AS VARBINARY(MAX))
    SELECT @myvarchar AS MyVarchar,@myvarbinary AS MyVarbinary, CAST(@myvarbinary AS VARCHAR(MAX)) AS VarcharValue
    --112233445566778899aabbccddeeff11223344 0x3131323233333434353536363737383839396161626263636464656566663131323233333434 112233445566778899aabbccddeeff11223344
    SELECT @myvarchar AS MyVarchar,@myvarbinary AS MyVarbinary, CONVERT(VARCHAR(MAX), @myvarbinary) AS VarcharValue
    --112233445566778899aabbccddeeff11223344 0x3131323233333434353536363737383839396161626263636464656566663131323233333434 112233445566778899aabbccddeeff11223344
    

    Also, here are the supporting links for my statement;

    http://msdn.microsoft.com/en-us/library/ms187928(SQL.100).aspx (MSDN Link)

    http://blogs.msdn.com/b/sqltips/archive/2008/07/02/converting-from-hex-string-to-varbinary-and-vice-versa.aspx?wa=wsignin1.0 (MSDN Blog)

    http://beyondrelational.com/blogs/jacob/archive/2009/06/13/converting-varbinary-to-varchar-using-for-xml.aspx (Jacob Article)

    All uses the same technique as described above.

    The thing here to understand is first you need to convert / cast your VARCHAR string to VARBINARY and then re-engineer the process to verify your logic.

    Please let me know if this doesn’t work for you. Hope I have answered you correctly.
     
    Thanks,
    Hasham

    Sunday, December 4, 2011 11:55 PM
    Answerer
  • SQLUSA !

    How you are sure that VARBINARY ''0x112233445566778899aabbccddeeff11223344'' is equivalent to VARCHAR ''112233445566778899aabbccddeeff11223344''.

    First we need to convert our input string to VARBINaRY and then reconvert it back to VARCHAr to test our logic.

    Also, see the links i have posted in my earlier post. It will help you understand where you are missing it.

    Thanks, Hasham

     

    Monday, December 5, 2011 1:15 AM
    Answerer
  • Sorry, I can't follow your logic. All I can say that my solution works beyond 16 bytes:

    DECLARE @hex varchar(max)=
        master.dbo.fn_varbintohexstr(convert(varbinary(max), 0x112233445566778899aabbccddeeff11223344556677))
    SELECT RIGHT(@hex,len(@hex)-2)
    -- 112233445566778899aabbccddeeff11223344556677
    


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Monday, December 5, 2011 1:23 AM
    Moderator
  • The CONVERT solution is not robust:

    I didn't read this thread from the beginning, so I don't know what it is all about. But there is a flaw in your code, since you don't specify the length for varchar value. Try:

    declare @k binary(20)
    set @k = 0x112233445566778899aabbccddeeff11223344
    select convert(varchar(40), @k, 2)

    It seems that you are suggesting a undocumented and unsupported function. That appears to be an inferior solution to me.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, December 5, 2011 8:59 AM
  • But the following does work....

    declare @k binary(20)
    set @k = 0x112233445566778899aabbccddeeff11223344
    select convert(varchar(38), @k, 2)
    

     

    I never meant for my answer to be the ultimate answer to the question, merely a way of proposing a possible alternative approach.  The OP needs to decide what suits their situation.

    Monday, December 5, 2011 9:23 AM
  • The solution posted by Kev Riley is the most appropriate, but it needs a slight tweak.

    As Kalman said this gives the incorrect output:

     

    declare @k binary(20)
    set @k = 0x112233445566778899aabbccddeeff11223344
    select convert(varchar, @k, 2)
    -- 112233445566778899AABBCCDDEEFF
    
    

     

    ...

     

     

    SELECT CONVERT(VARCHAR,0x112233445566778899aabbccddeeff11223344,2)	   --112233445566778899AABBCCDDEEFF
    SELECT CONVERT(VARCHAR(30),0x112233445566778899aabbccddeeff11223344,2) --112233445566778899AABBCCDDEEFF
    
    SELECT CONVERT(VARCHAR(38),0x112233445566778899aabbccddeeff11223344,2)  --112233445566778899AABBCCDDEEFF11223344
    SELECT CONVERT(VARCHAR(MAX),0x112233445566778899aabbccddeeff11223344,2) --112233445566778899AABBCCDDEEFF11223344
    

     

     

     

     

     

     

     


    Jon
    Monday, December 5, 2011 9:39 AM
  • Kalman, 

    exactly!  If the OP only wants to ever convert 16byte values (or 20byte values or 38byte values, or whatever) , then this is a possible, documented, supported solution.

    I am trying to give alternatives.

    • Proposed as answer by KEROBIN Monday, December 5, 2011 10:02 AM
    Monday, December 5, 2011 9:45 AM
  • That is fine. Let the OP decide which one meets the requirements. I agree with everybody that documented solution is preferred. Thanks all.
     
    The following 2 solutions appear to work based on limited testing:

    -- Using the CONVERT function
    SELECT CONVERT(VARCHAR(MAX),0x112233445566778899aabbccddeeff112233445566778899aabbccddeeff112233445566778899aabbccdd,2) 
    -- 112233445566778899AABBCCDDEEFF112233445566778899AABBCCDDEEFF112233445566778899AABBCCDD
    
    -- Undocumented function
    DECLARE @hex varchar(max)=
        master.dbo.fn_varbintohexstr(convert(varbinary(max), 0x112233445566778899aabbccddeeff112233445566778899aabbccddeeff112233445566778899aabbccdd))
    SELECT RIGHT(@hex,len(@hex)-2)
    -- 112233445566778899aabbccddeeff112233445566778899aabbccddeeff112233445566778899aabbccdd
     
     

    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM




    Monday, December 5, 2011 9:57 AM
    Moderator
  • The solution posted above does not work.  This is the result:

    112233445566778899AABBCCDDEEFF1122334400

      Yes, that was the input:

    declare @k binary(20)
    set @k = 0x112233445566778899aabbccddeeff11223344
    select convert(varchar(40), @k, 2), @k

    The two hex strings certainly looks similar to me. Yes, you did not type the last two 00, but since you declared @k as binary(20) (as opposed to varbinar(20)), they are there.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, December 5, 2011 12:03 PM
  • declare @k binary(20)
    set @k = 0x112233445566778899aabbccddeeff11223344
    select @k, convert(VARCHAR(256), @k, 2)
    -- 0x112233445566778899AABBCCDDEEFF1122334400  112233445566778899AABBCCDDEEFF1122334400

    It is robust... well almost. But remember that "VARCHAR" is equivalent to "VARCHAR(25)" so if You will convert number longer than 25 digits , You need to use longer string to convert to.

    About that "almost" part - convert might add additional zeroes at end... so using this method might not be best for Your solution. That is, if You need to get value 0x1 (in BINARY(2)) thinking it's 0x0001 and not 0x0100. Well if You will keep length of binary in check with value You use, it will be fine.



    • Edited by Kakkarot Friday, June 3, 2016 1:03 PM
    Friday, June 3, 2016 12:57 PM