Remove 0x from binary values using t-sql

Tüm Yanıtlar

• 

  

  1

  Oylamak İçin Oturum Aç

  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
  

  • Düzenleyen Kalman Toth 29 Kasım 2011 Salı 16:49

  29 Kasım 2011 Salı 16:47
• 

  

  1

  Oylamak İçin Oturum Aç

  declare @k binary(8)
  
  set @k = 0x000000000000048B
  
  select convert(varchar, @k, 2)
  

  
  

  • Yanıt Olarak Öneren Brian Tkatch 29 Kasım 2011 Salı 17:50
  • Yanıt Önerisini Geri Alan Kalman Toth 4 Aralık 2011 Pazar 22:02
  • Yanıt Olarak Öneren Jeff Moden 28 Mart 2020 Cumartesi 07:05

  29 Kasım 2011 Salı 16:58
• 

  

  0

  Oylamak İçin Oturum Aç

  And here i thought CONVERT() was only for dates.

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

  29 Kasım 2011 Salı 17:52
• 

  

  0

  Oylamak İçin Oturum Aç

  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 Aralık 2011 Pazar 22:04
• 

  

  0

  Oylamak İçin Oturum Aç

  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 Aralık 2011 Pazar 23:55

  Yanıtlayıcı
• 

  

  0

  Oylamak İçin Oturum Aç

  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 Aralık 2011 Pazartesi 01:15

  Yanıtlayıcı
• 

  

  0

  Oylamak İçin Oturum Aç

  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

  • Yanıt Olarak Öneren Naomi N 5 Aralık 2011 Pazartesi 01:38

  5 Aralık 2011 Pazartesi 01:23
• 

  

  0

  Oylamak İçin Oturum Aç

  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 Aralık 2011 Pazartesi 08:59
• 

  

  0

  Oylamak İçin Oturum Aç

  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.

  • Yanıt Olarak Öneren Jeff Moden 28 Mart 2020 Cumartesi 07:08

  5 Aralık 2011 Pazartesi 09:23
• 

  

  0

  Oylamak İçin Oturum Aç

  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
  

  • Düzenleyen Jon Gurgul 5 Aralık 2011 Pazartesi 09:39
  • Yanıt Olarak Öneren Naomi N 6 Aralık 2011 Salı 03:46

  5 Aralık 2011 Pazartesi 09:39
• 

  

  0

  Oylamak İçin Oturum Aç

  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.

  • Yanıt Olarak Öneren KEROBIN 5 Aralık 2011 Pazartesi 10:02

  5 Aralık 2011 Pazartesi 09:45
• 

  

  0

  Oylamak İçin Oturum Aç

  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
  
  

  
  
  
  

  • Düzenleyen Kalman Toth 5 Aralık 2011 Pazartesi 14:08

  5 Aralık 2011 Pazartesi 09:57
• 

  

  0

  Oylamak İçin Oturum Aç

  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 Aralık 2011 Pazartesi 12:03
• 

  

  0

  Oylamak İçin Oturum Aç

  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.

  
  
  

  • Düzenleyen Kakkarot 3 Haziran 2016 Cuma 13:03

  3 Haziran 2016 Cuma 12:57
• 

  

  0

  Oylamak İçin Oturum Aç

  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 Mart 2020 Cumartesi 07:13
• 

  

  0

  Oylamak İçin Oturum Aç

  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

  
  

  • Düzenleyen Jeff Moden 28 Mart 2020 Cumartesi 07:23

  28 Mart 2020 Cumartesi 07:22