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

ตอบทั้งหมด

  • 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
    • แก้ไขโดย Kalman Toth 29 พฤศจิกายน 2554 16:49
    29 พฤศจิกายน 2554 16:47
  • declare @k binary(8)
    
    set @k = 0x000000000000048B
    
    select convert(varchar, @k, 2)
    

    • เสนอเป็นคำตอบโดย Brian Tkatch 29 พฤศจิกายน 2554 17:50
    • ยกเลิกการนำเสนอเป็นคำตอบโดย Kalman Toth 4 ธันวาคม 2554 22:02
    • เสนอเป็นคำตอบโดย Jeff Moden 28 มีนาคม 2563 7:05
    29 พฤศจิกายน 2554 16:58
  • And here i thought CONVERT() was only for dates.

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

    29 พฤศจิกายน 2554 17:52
  • 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
    4 ธันวาคม 2554 22:04
  • 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

    4 ธันวาคม 2554 23:55
    ผู้ตอบ
  • 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

     

    5 ธันวาคม 2554 1:15
    ผู้ตอบ
  • 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
    • เสนอเป็นคำตอบโดย Naomi N 5 ธันวาคม 2554 1:38
    5 ธันวาคม 2554 1:23
  • 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
    5 ธันวาคม 2554 8:59
  • 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.

    • เสนอเป็นคำตอบโดย Jeff Moden 28 มีนาคม 2563 7:08
    5 ธันวาคม 2554 9:23
  • 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
    • แก้ไขโดย Jon Gurgul 5 ธันวาคม 2554 9:39
    • เสนอเป็นคำตอบโดย Naomi N 6 ธันวาคม 2554 3:46
    5 ธันวาคม 2554 9:39
  • 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.

    • เสนอเป็นคำตอบโดย KEROBIN 5 ธันวาคม 2554 10:02
    5 ธันวาคม 2554 9:45
  • 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




    • แก้ไขโดย Kalman Toth 5 ธันวาคม 2554 14:08
    5 ธันวาคม 2554 9:57
  • 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
    5 ธันวาคม 2554 12:03
  • 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.



    • แก้ไขโดย Kakkarot 3 มิถุนายน 2559 13:03
    3 มิถุนายน 2559 12:57
  • 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

    28 มีนาคม 2563 7:13
  • 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


    • แก้ไขโดย Jeff Moden 28 มีนาคม 2563 7:23
    28 มีนาคม 2563 7:22