Convert integer to hexa in sql












-1














I want to convert int to hexadecimal in SQL.



Example:



SELECT CONVERT(VARBINARY(8), 162)



Result :0x000000A2




Actual value is this




A2




Why I am getting unnecessary part at prefix?



Can I remove previous part?



What is right way to handle it?






sql-server







share|improve this question
























  • What unnecessary part? That value looks correct for a varbinary to me
    – Larnu
    Nov 22 at 10:41






  • 1




    Possible duplicate of Convert integer to hex and hex to integer
    – AlexK
    Nov 22 at 10:50
















-1














I want to convert int to hexadecimal in SQL.



Example:



SELECT CONVERT(VARBINARY(8), 162)



Result :0x000000A2




Actual value is this




A2




Why I am getting unnecessary part at prefix?



Can I remove previous part?



What is right way to handle it?






sql-server







share|improve this question
























  • What unnecessary part? That value looks correct for a varbinary to me
    – Larnu
    Nov 22 at 10:41






  • 1




    Possible duplicate of Convert integer to hex and hex to integer
    – AlexK
    Nov 22 at 10:50














-1












-1








-1







I want to convert int to hexadecimal in SQL.



Example:



SELECT CONVERT(VARBINARY(8), 162)



Result :0x000000A2




Actual value is this




A2




Why I am getting unnecessary part at prefix?



Can I remove previous part?



What is right way to handle it?






sql-server







share|improve this question















I want to convert int to hexadecimal in SQL.



Example:



SELECT CONVERT(VARBINARY(8), 162)



Result :0x000000A2




Actual value is this




A2




Why I am getting unnecessary part at prefix?



Can I remove previous part?



What is right way to handle it?






sql-server




sql-server






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 22 at 11:53









Birel

436113




436113










asked Nov 22 at 10:36









Pinky

64




64












  • What unnecessary part? That value looks correct for a varbinary to me
    – Larnu
    Nov 22 at 10:41






  • 1




    Possible duplicate of Convert integer to hex and hex to integer
    – AlexK
    Nov 22 at 10:50


















  • What unnecessary part? That value looks correct for a varbinary to me
    – Larnu
    Nov 22 at 10:41






  • 1




    Possible duplicate of Convert integer to hex and hex to integer
    – AlexK
    Nov 22 at 10:50
















What unnecessary part? That value looks correct for a varbinary to me
– Larnu
Nov 22 at 10:41




What unnecessary part? That value looks correct for a varbinary to me
– Larnu
Nov 22 at 10:41




1




1




Possible duplicate of Convert integer to hex and hex to integer
– AlexK
Nov 22 at 10:50




Possible duplicate of Convert integer to hex and hex to integer
– AlexK
Nov 22 at 10:50












3 Answers
3






active

oldest

votes


















2














To quote the documentation:




When other data types are converted to binary or varbinary, the data is padded or truncated on the left. Padding is achieved by using hexadecimal zeros.




You're specifying VARBINARY(8) in your query, so the result is padded with zeros to that length. If you need the value without the padding for some reason, specify VARBINARY(1), which will return 0xA2.



Note: They're both the same value



Alternatively, if you just want a 2 character string:



SELECT RIGHT(CONVERT(VARCHAR(8),CONVERT(VARBINARY(8),162),2), 2)



Which will return A2






share|improve this answer























  • It work for me.PRINT CONVERT(VARCHAR(8),CONVERT(VARBINARY(1), 162),2)
    – Pinky
    Nov 22 at 11:27






  • 1




    @Pinky, okay, as long as you know the length of the significant figures
    – Jodrell
    Nov 22 at 11:36



















0














At a complete a total guess in the absence of any response from the OP:



SELECT V.BloatedHex,

       ISNULL(STUFF(V.BloatedHex,1,NULLIF(PATINDEX('%[^0]%',V.BloatedHex),0)-1,''),0)

FROM (VALUES(STUFF(CONVERT(varchar(10),CONVERT(varbinary(8),162),1),1,2,''))) V(BloatedHex);


This returns the varchar(10) value 'A2'.






share|improve this answer





























    0














    You can do it in one statement like this,



    DECLARE @someNumber BIGINT = 162;
    

    
WITH Hex AS (
    
SELECT CONVERT(VARCHAR(34), CONVERT(VARBINARY(8), @someNumber), 2) [Value]
    
)
    
SELECT SUBSTRING([Value], PATINDEX('%[^0]%', [Value]), 34) FROM Hex


    ;



    This does not use any unsupported internal functions and attempts to minimize string manipulation.



    Better still, don't write this kind of presentation code with TSQL, it is not what it is good at. Worry about making it look pretty when you display the value to the user.






    share|improve this answer





















      Your Answer






      StackExchange.ifUsing("editor", function () {
      StackExchange.using("externalEditor", function () {
      StackExchange.using("snippets", function () {
      StackExchange.snippets.init();
      });
      });
      }, "code-snippets");

      StackExchange.ready(function() {
      var channelOptions = {
      tags: "".split(" "),
      id: "1"
      };
      initTagRenderer("".split(" "), "".split(" "), channelOptions);

      StackExchange.using("externalEditor", function() {
      // Have to fire editor after snippets, if snippets enabled
      if (StackExchange.settings.snippets.snippetsEnabled) {
      StackExchange.using("snippets", function() {
      createEditor();
      });
      }
      else {
      createEditor();
      }
      });

      function createEditor() {
      StackExchange.prepareEditor({
      heartbeatType: 'answer',
      autoActivateHeartbeat: false,
      convertImagesToLinks: true,
      noModals: true,
      showLowRepImageUploadWarning: true,
      reputationToPostImages: 10,
      bindNavPrevention: true,
      postfix: "",
      imageUploader: {
      brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
      contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
      allowUrls: true
      },
      onDemand: true,
      discardSelector: ".discard-answer"
      ,immediatelyShowMarkdownHelp:true
      });


      }
      });














      draft saved

      draft discarded


















      StackExchange.ready(
      function () {
      StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53429026%2fconvert-integer-to-hexa-in-sql%23new-answer', 'question_page');
      }
      );

      Post as a guest















      Required, but never shown

























      3 Answers
      3






      active

      oldest

      votes








      3 Answers
      3






      active

      oldest

      votes









      active

      oldest

      votes






      active

      oldest

      votes









      2














      To quote the documentation:




      When other data types are converted to binary or varbinary, the data is padded or truncated on the left. Padding is achieved by using hexadecimal zeros.




      You're specifying VARBINARY(8) in your query, so the result is padded with zeros to that length. If you need the value without the padding for some reason, specify VARBINARY(1), which will return 0xA2.



      Note: They're both the same value



      Alternatively, if you just want a 2 character string:



      SELECT RIGHT(CONVERT(VARCHAR(8),CONVERT(VARBINARY(8),162),2), 2)



      Which will return A2






      share|improve this answer























      • It work for me.PRINT CONVERT(VARCHAR(8),CONVERT(VARBINARY(1), 162),2)
        – Pinky
        Nov 22 at 11:27






      • 1




        @Pinky, okay, as long as you know the length of the significant figures
        – Jodrell
        Nov 22 at 11:36
















      2














      To quote the documentation:




      When other data types are converted to binary or varbinary, the data is padded or truncated on the left. Padding is achieved by using hexadecimal zeros.




      You're specifying VARBINARY(8) in your query, so the result is padded with zeros to that length. If you need the value without the padding for some reason, specify VARBINARY(1), which will return 0xA2.



      Note: They're both the same value



      Alternatively, if you just want a 2 character string:



      SELECT RIGHT(CONVERT(VARCHAR(8),CONVERT(VARBINARY(8),162),2), 2)



      Which will return A2






      share|improve this answer























      • It work for me.PRINT CONVERT(VARCHAR(8),CONVERT(VARBINARY(1), 162),2)
        – Pinky
        Nov 22 at 11:27






      • 1




        @Pinky, okay, as long as you know the length of the significant figures
        – Jodrell
        Nov 22 at 11:36














      2












      2








      2






      To quote the documentation:




      When other data types are converted to binary or varbinary, the data is padded or truncated on the left. Padding is achieved by using hexadecimal zeros.




      You're specifying VARBINARY(8) in your query, so the result is padded with zeros to that length. If you need the value without the padding for some reason, specify VARBINARY(1), which will return 0xA2.



      Note: They're both the same value



      Alternatively, if you just want a 2 character string:



      SELECT RIGHT(CONVERT(VARCHAR(8),CONVERT(VARBINARY(8),162),2), 2)



      Which will return A2






      share|improve this answer














      To quote the documentation:




      When other data types are converted to binary or varbinary, the data is padded or truncated on the left. Padding is achieved by using hexadecimal zeros.




      You're specifying VARBINARY(8) in your query, so the result is padded with zeros to that length. If you need the value without the padding for some reason, specify VARBINARY(1), which will return 0xA2.



      Note: They're both the same value



      Alternatively, if you just want a 2 character string:



      SELECT RIGHT(CONVERT(VARCHAR(8),CONVERT(VARBINARY(8),162),2), 2)



      Which will return A2







      share|improve this answer














      share|improve this answer



      share|improve this answer








      edited Nov 22 at 11:22

























      answered Nov 22 at 10:43









      Diado

      1,37021015




      1,37021015












      • It work for me.PRINT CONVERT(VARCHAR(8),CONVERT(VARBINARY(1), 162),2)
        – Pinky
        Nov 22 at 11:27






      • 1




        @Pinky, okay, as long as you know the length of the significant figures
        – Jodrell
        Nov 22 at 11:36


















      • It work for me.PRINT CONVERT(VARCHAR(8),CONVERT(VARBINARY(1), 162),2)
        – Pinky
        Nov 22 at 11:27






      • 1




        @Pinky, okay, as long as you know the length of the significant figures
        – Jodrell
        Nov 22 at 11:36
















      It work for me.PRINT CONVERT(VARCHAR(8),CONVERT(VARBINARY(1), 162),2)
      – Pinky
      Nov 22 at 11:27




      It work for me.PRINT CONVERT(VARCHAR(8),CONVERT(VARBINARY(1), 162),2)
      – Pinky
      Nov 22 at 11:27




      1




      1




      @Pinky, okay, as long as you know the length of the significant figures
      – Jodrell
      Nov 22 at 11:36




      @Pinky, okay, as long as you know the length of the significant figures
      – Jodrell
      Nov 22 at 11:36













      0














      At a complete a total guess in the absence of any response from the OP:



      SELECT V.BloatedHex,
      
       ISNULL(STUFF(V.BloatedHex,1,NULLIF(PATINDEX('%[^0]%',V.BloatedHex),0)-1,''),0)
      
FROM (VALUES(STUFF(CONVERT(varchar(10),CONVERT(varbinary(8),162),1),1,2,''))) V(BloatedHex);


      This returns the varchar(10) value 'A2'.






      share|improve this answer


























        0














        At a complete a total guess in the absence of any response from the OP:



        SELECT V.BloatedHex,
        
       ISNULL(STUFF(V.BloatedHex,1,NULLIF(PATINDEX('%[^0]%',V.BloatedHex),0)-1,''),0)
        
FROM (VALUES(STUFF(CONVERT(varchar(10),CONVERT(varbinary(8),162),1),1,2,''))) V(BloatedHex);


        This returns the varchar(10) value 'A2'.






        share|improve this answer
























          0












          0








          0






          At a complete a total guess in the absence of any response from the OP:



          SELECT V.BloatedHex,
          
       ISNULL(STUFF(V.BloatedHex,1,NULLIF(PATINDEX('%[^0]%',V.BloatedHex),0)-1,''),0)
          
FROM (VALUES(STUFF(CONVERT(varchar(10),CONVERT(varbinary(8),162),1),1,2,''))) V(BloatedHex);


          This returns the varchar(10) value 'A2'.






          share|improve this answer












          At a complete a total guess in the absence of any response from the OP:



          SELECT V.BloatedHex,
          
       ISNULL(STUFF(V.BloatedHex,1,NULLIF(PATINDEX('%[^0]%',V.BloatedHex),0)-1,''),0)
          
FROM (VALUES(STUFF(CONVERT(varchar(10),CONVERT(varbinary(8),162),1),1,2,''))) V(BloatedHex);


          This returns the varchar(10) value 'A2'.







          share|improve this answer












          share|improve this answer



          share|improve this answer










          answered Nov 22 at 10:50









          Larnu

          15.1k41530




          15.1k41530























              0














              You can do it in one statement like this,



              DECLARE @someNumber BIGINT = 162;
              

              
WITH Hex AS (
              
SELECT CONVERT(VARCHAR(34), CONVERT(VARBINARY(8), @someNumber), 2) [Value]
              
)
              
SELECT SUBSTRING([Value], PATINDEX('%[^0]%', [Value]), 34) FROM Hex


              ;



              This does not use any unsupported internal functions and attempts to minimize string manipulation.



              Better still, don't write this kind of presentation code with TSQL, it is not what it is good at. Worry about making it look pretty when you display the value to the user.






              share|improve this answer


























                0














                You can do it in one statement like this,



                DECLARE @someNumber BIGINT = 162;
                

                
WITH Hex AS (
                
SELECT CONVERT(VARCHAR(34), CONVERT(VARBINARY(8), @someNumber), 2) [Value]
                
)
                
SELECT SUBSTRING([Value], PATINDEX('%[^0]%', [Value]), 34) FROM Hex


                ;



                This does not use any unsupported internal functions and attempts to minimize string manipulation.



                Better still, don't write this kind of presentation code with TSQL, it is not what it is good at. Worry about making it look pretty when you display the value to the user.






                share|improve this answer
























                  0












                  0








                  0






                  You can do it in one statement like this,



                  DECLARE @someNumber BIGINT = 162;
                  

                  
WITH Hex AS (
                  
SELECT CONVERT(VARCHAR(34), CONVERT(VARBINARY(8), @someNumber), 2) [Value]
                  
)
                  
SELECT SUBSTRING([Value], PATINDEX('%[^0]%', [Value]), 34) FROM Hex


                  ;



                  This does not use any unsupported internal functions and attempts to minimize string manipulation.



                  Better still, don't write this kind of presentation code with TSQL, it is not what it is good at. Worry about making it look pretty when you display the value to the user.






                  share|improve this answer












                  You can do it in one statement like this,



                  DECLARE @someNumber BIGINT = 162;
                  

                  
WITH Hex AS (
                  
SELECT CONVERT(VARCHAR(34), CONVERT(VARBINARY(8), @someNumber), 2) [Value]
                  
)
                  
SELECT SUBSTRING([Value], PATINDEX('%[^0]%', [Value]), 34) FROM Hex


                  ;



                  This does not use any unsupported internal functions and attempts to minimize string manipulation.



                  Better still, don't write this kind of presentation code with TSQL, it is not what it is good at. Worry about making it look pretty when you display the value to the user.







                  share|improve this answer












                  share|improve this answer



                  share|improve this answer










                  answered Nov 22 at 11:30









                  Jodrell

                  26.3k35794




                  26.3k35794






























                      draft saved

                      draft discarded




















































                      Thanks for contributing an answer to Stack Overflow!


                      • Please be sure to answer the question. Provide details and share your research!

                      But avoid …



                      • Asking for help, clarification, or responding to other answers.

                      • Making statements based on opinion; back them up with references or personal experience.


                      To learn more, see our tips on writing great answers.





                      Some of your past answers have not been well-received, and you're in danger of being blocked from answering.


                      Please pay close attention to the following guidance:


                      • Please be sure to answer the question. Provide details and share your research!

                      But avoid …



                      • Asking for help, clarification, or responding to other answers.

                      • Making statements based on opinion; back them up with references or personal experience.


                      To learn more, see our tips on writing great answers.




                      draft saved


                      draft discarded














                      StackExchange.ready(
                      function () {
                      StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53429026%2fconvert-integer-to-hexa-in-sql%23new-answer', 'question_page');
                      }
                      );

                      Post as a guest















                      Required, but never shown





















































                      Required, but never shown














                      Required, but never shown












                      Required, but never shown







                      Required, but never shown

































                      Required, but never shown














                      Required, but never shown












                      Required, but never shown







                      Required, but never shown







                      -

                      Popular posts from this blog

                      Berounka

                      Image
                      Berounka.mw-parser-output .entete.map{background-image:url("//upload.wikimedia.org/wikipedia/commons/7/7a/Picto_infobox_map.png")} Caractéristiques Longueur 139  km Bassin 8 861  km 2 Bassin collecteur Elbe Débit moyen ? Cours Source Confluence de la Mže et de la Radbuza · Localisation Pilsen · Altitude 305  m · Coordonnées 49° 45′ 13″ N, 13° 23′ 24″ E Confluence Vltava · Localisation Prague · Altitude 187  m · Coordonnées 49° 59′ 43″ N, 14° 24′ 04″ E Géographie Pays traversés   République tchèque modifier   La Berounka est une rivière de la République tchèque. Elle porte le nom de Mže depuis sa source en Allemagne près de la frontière jusqu'à sa confluence avec la Radbuza à Plzeň. Elle continue ensuite sous le nom de Berounka jusqu'à sa confluence avec la Vltava, près de Prague. La rivière est une destination prisée pour les amateurs de canoë, qui appréc...
                      Read more

                      Sphinx de Gizeh

                      Image
                      Pour les articles homonymes, voir Sphinx. Sphinx de Gizeh.mw-parser-output .entete.egypte-antique{background-image:url("//upload.wikimedia.org/wikipedia/commons/c/cc/Picto_infobox_ancient_Egypt.png")} Site d'Égypte antique Le visage du sphinx de Gizeh. Noms en arabe أبو الهول , Abou al-Hôl Localisation Région Basse-Égypte Coordonnées 29° 58′ 31″ nord, 31° 08′ 16″ est Géolocalisation sur la carte : Égypte Sphinx de Gizeh modifier   Le sphinx de Gizeh est la statue thérianthrope qui se dresse devant les grandes pyramides du plateau de Gizeh, en Basse-Égypte. Sculpture monumentale monolithique la plus grande du monde [ 1 ] avec 73,5 mètres de longueur, 14 mètres de largeur et 20,22 mètres de hauteur, elle représente un sphinx couchant. Réalisée vers -2500, elle serait attribuée à Djédefrê, l'un des pharaons de la IV e  dynastie, représentant son père le pharaon Khéops. Sommaire 1 Étymologie 2 Desc...
                      Read more

                      Different font size/position of beamer's navigation symbols template's content depending on regular/plain...

                      Image
                      3 Let's suppose I'd like to divert the navigation symbols template (e.g. in order replace them by the frame numbers, see this bug report ;) I would be facing troubles regarding the font size/position, depending on regular/plain frames, as shown by the following MCE. How could I get rid of these troubles? documentclass{beamer} usetheme{PaloAlto} setbeamertemplate{navigation symbols}{insertframenumber/inserttotalframenumber} begin{document} begin{frame} frametitle{A regular frame} end{frame} begin{frame}[plain] frametitle{A plain frame} end{frame} end{document} beamer header-footer share | improve this question asked Dec 10 '18 a...
                      Read more