locked
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
    • Edited by Kalman Toth Tuesday, November 29, 2011 4:49 PM
    Tuesday, November 29, 2011 4:47 PM
  • declare @k binary(8)
    
    set @k = 0x000000000000048B
    
    select convert(varchar, @k, 2)
    

    • Proposed as answer by Brian Tkatch Tuesday, November 29, 2011 5:50 PM
    • Unproposed as answer by Kalman Toth Sunday, December 4, 2011 10:02 PM
    • Proposed as answer by Jeff Moden Saturday, March 28, 2020 7:05 AM
    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
  • 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
  • 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
    • Proposed as answer by Naomi N Monday, December 5, 2011 1:38 AM
    Monday, December 5, 2011 1:23 AM
  • 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.

    • Proposed as answer by Jeff Moden Saturday, March 28, 2020 7:08 AM
    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
    • Edited by Jon Gurgul Monday, December 5, 2011 9:39 AM
    • Proposed as answer by Naomi N Tuesday, December 6, 2011 3:46 AM
    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




    • Edited by Kalman Toth Monday, December 5, 2011 2:08 PM
    Monday, December 5, 2011 9:57 AM
  • 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
  • I don't know why Kev Riley's first answer was unproposed as an answer... it doesn't take much imagination to know that if you have something bigger, you just need to define the width of the VARCHAR to be able to handle it.  One should also take the time to read the documentation on CONVERT.

    Heh... remember... "There is no spoon" and that includes "silver spoons". :D

    And, yeah... I know I'm necroing a nearly decade old post.


    --Jeff Moden

    Saturday, March 28, 2020 7:13 AM
  • 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.



    It's not the CONVERT that will add the extra zeros.  It's the fact that someone used a non variable width BINARY() to insert the original data into.  Run the following code to prove it.

    DECLARE  @BinFixed BINARY(20)    = 0x01
            ,@BinVar   VARBINARY(20) = 0x01
    ;
     SELECT  BinFixedConv = CONVERT(VARCHAR(50),@BinFixed,2)
            ,BinVarConv  = CONVERT(VARCHAR(50),@BinVar,2)
    ;
     SELECT  BinFixedNoConv = @BinFixed
            ,BinVarNoConv   = @BinVar
    ;


    --Jeff Moden


    • Edited by Jeff Moden Saturday, March 28, 2020 7:23 AM
    Saturday, March 28, 2020 7:22 AM