Convert integer to hex and hex to integer

by

Convert INT to hex:

SELECT CONVERT(VARBINARY(8), 16777215)

Convert hex to INT:

SELECT CONVERT(INT, 0xFFFFFF)

Update 2015-03-16

The above example has the limitation that it only works when the HEX value is given as an integer literal. For completeness, if the value to convert is a hexadecimal string (such as found in a varchar column) use:

-- If the '0x' marker is present:
SELECT CONVERT(INT, CONVERT(VARBINARY, '0x1FFFFF', 1))

-- If the '0x' marker is NOT present:
SELECT CONVERT(INT, CONVERT(VARBINARY, '1FFFFF', 2))

Note: The string must contain an even number of hex digits. An odd number of digits will yield an error.

More details can be found in the “Binary Styles” section of CAST and CONVERT (Transact-SQL). I believe SQL Server 2008 or later is required.

More Related Contents:

  1. Case statement for multiple columns
  2. SQL where clause odd behavior [closed]
  3. SQL Server Exec issue [closed]
  4. How do I obtain a Query Execution Plan in SQL Server?
  5. Count(*) vs Count(1) – SQL Server
  6. Selecting data from two different servers in SQL Server
  7. Is a view faster than a simple query?
  8. How to rewrite IS DISTINCT FROM and IS NOT DISTINCT FROM in SQL Server 20008R2?
  9. SQL Server: Make all UPPER case to Proper Case/Title Case
  10. How do I delete from multiple tables using INNER JOIN in SQL server
  11. Does Foreign Key improve query performance?
  12. SQL : BETWEEN vs =
  13. SQL Server : dynamic pivot over 5 columns
  14. What’s the best way to capitalise the first letter of each word in a string in SQL Server
  15. For Nvarchar(Max) I am only getting 4000 characters in TSQL?
  16. Postgres Case Sensitivity
  17. Splitting the string in sql server
  18. How do I query for all dates greater than a certain date in SQL Server?
  19. Check If the string contains accented characters in SQL?
  20. Count based on condition in SQL Server
  21. Does Sql JOIN order affect performance?
  22. Best equivalent for IsInteger in SQL Server
  23. Syntax of for-loop in SQL Server
  24. Using tuples in SQL “IN” clause
  25. Why does ISNUMERIC(‘.’) return 1?
  26. How do you copy a record in a SQL table but swap out the unique id of the new row?
  27. Cumulative Total in MS Sql server [duplicate]
  28. What does ON [PRIMARY] mean?
  29. Export table data from one SQL Server to another
  30. Getting warning: Null value is eliminated by an aggregate or other SET operation

Leave a Comment