Comments on: SQL SERVER – Question – How to Convert Hex to Decimalhttp://blog.sqlauthority.com/2010/02/01/sql-server-question-how-to-convert-hex-to-decimal/
SQL, SQL Server, MySQL, Big Data and NoSQLTue, 03 Mar 2015 15:02:07 +0000hourly1http://wordpress.com/By: SQL SERVER – Weekly Series – Memory Lane – #039 | Journey to SQL Authority with Pinal Davehttp://blog.sqlauthority.com/2010/02/01/sql-server-question-how-to-convert-hex-to-decimal/#comment-537268
Sat, 27 Jul 2013 01:32:02 +0000http://blog.sqlauthority.com/?p=7785#comment-537268[…] How to Convert Hex to Decimal or INT Earlier I asked regarding a question about how to convert Hex to Decimal. I promised that I will post an answer with Due Credit to the author but never got around to post a blog post around it. Read the original post over here SQL SERVER – Question – How to Convert Hex to Decimal. […]

The TCP/IP connection to the host 11.01.0.45, port 1433 has failed. Error: “Address already in use: connect. Verify the connection properties, check that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port, and that no firewall is blocking TCP connections to the port.”.
1000
com.websym.common.exception.DBException: org.hibernate.exception.JDBCConnectionException: Cannot open connection

,case when left(substring(ip,1,2),1) =’A’ Then 10 * 16
when left(substring(ip,1,2),1) =’B’ Then 11 * 16
when left(substring(ip,1,2),1) =’C’ Then 12 * 16
when left(substring(ip,1,2),1) =’D’ Then 13 * 16
when left(substring(ip,1,2),1) =’E’ Then 14 * 16
when left(substring(ip,1,2),1) =’F’ Then 15 * 16
ELSE left(substring(ip,1,2),1) * 16
END
+
case when Right(substring(ip,1,2),1) =’A’ Then 10
when Right(substring(ip,1,2),1) =’B’ Then 11
when Right(substring(ip,1,2),1) =’C’ Then 12
when Right(substring(ip,1,2),1) =’D’ Then 13
when Right(substring(ip,1,2),1) =’E’ Then 14
when Right(substring(ip,1,2),1) =’F’ Then 15
ELSE Right(substring(ip,1,2),1)
END AS A

,case when left(substring(ip,len(substring(ip,1,2))+1,2),1) =’A’ Then 10 * 16
when left(substring(ip,len(substring(ip,1,2))+1,2),1) =’B’ Then 11 * 16
when left(substring(ip,len(substring(ip,1,2))+1,2),1) =’C’ Then 12 * 16
when left(substring(ip,len(substring(ip,1,2))+1,2),1) =’D’ Then 13 * 16
when left(substring(ip,len(substring(ip,1,2))+1,2),1) =’E’ Then 14 * 16
when left(substring(ip,len(substring(ip,1,2))+1,2),1) =’F’ Then 15 * 16
ELSE left(substring(ip,len(substring(ip,1,2))+1,2),1) * 16
END
+
case when Right(substring(ip,len(substring(ip,1,2))+1,2),1) =’A’ Then 10
when Right(substring(ip,len(substring(ip,1,2))+1,2),1) =’B’ Then 11
when Right(substring(ip,len(substring(ip,1,2))+1,2),1) =’C’ Then 12
when Right(substring(ip,len(substring(ip,1,2))+1,2),1) =’D’ Then 13
when Right(substring(ip,len(substring(ip,1,2))+1,2),1) =’E’ Then 14
when Right(substring(ip,len(substring(ip,1,2))+1,2),1) =’F’ Then 15
ELSE Right(substring(ip,len(substring(ip,1,2))+1,2),1)
END AS B

,case when left(substring(ip,len(substring(ip,len(substring(ip,1,2))+1,2))+3,2),1) =’A’ Then 10 * 16
when left(substring(ip,len(substring(ip,len(substring(ip,1,2))+1,2))+3,2),1) =’B’ Then 11 * 16
when left(substring(ip,len(substring(ip,len(substring(ip,1,2))+1,2))+3,2),1) =’C’ Then 12 * 16
when left(substring(ip,len(substring(ip,len(substring(ip,1,2))+1,2))+3,2),1) =’D’ Then 13 * 16
when left(substring(ip,len(substring(ip,len(substring(ip,1,2))+1,2))+3,2),1) =’E’ Then 14 * 16
when left(substring(ip,len(substring(ip,len(substring(ip,1,2))+1,2))+3,2),1) =’F’ Then 15 * 16
ELSE left(substring(ip,len(substring(ip,len(substring(ip,1,2))+1,2))+3,2),1) * 16
END
+
case when Right(substring(ip,len(substring(ip,len(substring(ip,1,2))+1,2))+3,2),1) =’A’ Then 10
when Right(substring(ip,len(substring(ip,len(substring(ip,1,2))+1,2))+3,2),1) =’B’ Then 11
when Right(substring(ip,len(substring(ip,len(substring(ip,1,2))+1,2))+3,2),1) =’C’ Then 12
when Right(substring(ip,len(substring(ip,len(substring(ip,1,2))+1,2))+3,2),1) =’D’ Then 13
when Right(substring(ip,len(substring(ip,len(substring(ip,1,2))+1,2))+3,2),1) =’E’ Then 14
when Right(substring(ip,len(substring(ip,len(substring(ip,1,2))+1,2))+3,2),1) =’F’ Then 15
ELSE Right(substring(ip,len(substring(ip,len(substring(ip,1,2))+1,2))+3,2),1)
END AS C

,case when left(substring(ip,len(substring(ip,len(substring(ip,len(substring(ip,1,2))+1,2))+3,2))+5,2),1) =’A’ Then 10 * 16
when left(substring(ip,len(substring(ip,len(substring(ip,len(substring(ip,1,2))+1,2))+3,2))+5,2),1) =’B’ Then 11 * 16
when left(substring(ip,len(substring(ip,len(substring(ip,len(substring(ip,1,2))+1,2))+3,2))+5,2),1) =’C’ Then 12 * 16
when left(substring(ip,len(substring(ip,len(substring(ip,len(substring(ip,1,2))+1,2))+3,2))+5,2),1) =’D’ Then 13 * 16
when left(substring(ip,len(substring(ip,len(substring(ip,len(substring(ip,1,2))+1,2))+3,2))+5,2),1) =’E’ Then 14 * 16
when left(substring(ip,len(substring(ip,len(substring(ip,len(substring(ip,1,2))+1,2))+3,2))+5,2),1) =’F’ Then 15 * 16
ELSE left(substring(ip,len(substring(ip,len(substring(ip,len(substring(ip,1,2))+1,2))+3,2))+5,2),1) * 16
END
+
case when Right(substring(ip,len(substring(ip,len(substring(ip,len(substring(ip,1,2))+1,2))+3,2))+5,2),1) =’A’ Then 10
when Right(substring(ip,len(substring(ip,len(substring(ip,len(substring(ip,1,2))+1,2))+3,2))+5,2),1) =’B’ Then 11
when Right(substring(ip,len(substring(ip,len(substring(ip,len(substring(ip,1,2))+1,2))+3,2))+5,2),1) =’C’ Then 12
when Right(substring(ip,len(substring(ip,len(substring(ip,len(substring(ip,1,2))+1,2))+3,2))+5,2),1) =’D’ Then 13
when Right(substring(ip,len(substring(ip,len(substring(ip,len(substring(ip,1,2))+1,2))+3,2))+5,2),1) =’E’ Then 14
when Right(substring(ip,len(substring(ip,len(substring(ip,len(substring(ip,1,2))+1,2))+3,2))+5,2),1) =’F’ Then 15
ELSE Right(substring(ip,len(substring(ip,len(substring(ip,len(substring(ip,1,2))+1,2))+3,2))+5,2),1)
END AS D
INTO #temp
from #t

]]>By: SQL SERVER – Answer – How to Convert Hex to Decimal or INT « SQL Server Journey with SQL Authorityhttp://blog.sqlauthority.com/2010/02/01/sql-server-question-how-to-convert-hex-to-decimal/#comment-319077
Thu, 26 Jul 2012 01:30:46 +0000http://blog.sqlauthority.com/?p=7785#comment-319077[…] Has it ever happened to you that you say something but forget to follow up due to any reason? It usually does not happen to me as I try to remember everything in my task list but there is always an exception. Last year I asked a question regarding about how to convert Hex to Decimal. I promised that I will post an answer with Due Credit to the author but never got around to post a blog post around it. Read the original post over here SQL SERVER – Question – How to Convert Hex to Decimal. […]

]]>By: aspdotnetcsharpsntoshhttp://blog.sqlauthority.com/2010/02/01/sql-server-question-how-to-convert-hex-to-decimal/#comment-261106
Fri, 09 Mar 2012 13:56:09 +0000http://blog.sqlauthority.com/?p=7785#comment-261106hi.
i want to convert hex to readable string how to do pls help me its very urgent.

i have hex like “0605040B8423F0660601AE02056A00″
want to convert in human readable string.

]]>By: Rayhttp://blog.sqlauthority.com/2010/02/01/sql-server-question-how-to-convert-hex-to-decimal/#comment-172600
Tue, 27 Sep 2011 20:34:43 +0000http://blog.sqlauthority.com/?p=7785#comment-172600A Little late to the party, however when reading books online for convert I noticed you could apply styles when converting to binary.

SELECT CONVERT(VARBINARY(8), 65535),CAST(0x0000FFFF AS INT),
— If the value is a string without the 0x
CAST(CONVERT(VARBINARY, ‘ffff’, 2) AS INT),
CAST(CONVERT(VARBINARY, ‘0000ffff’, 2) AS INT),
— or if your string has the 0x
CAST(CONVERT(VARBINARY, ‘0x0000FFFF’, 1) AS INT),
CAST(CONVERT(VARBINARY, ‘0xFFFF’, 1) AS INT)

]]>By: Vedran Kesegichttp://blog.sqlauthority.com/2010/02/01/sql-server-question-how-to-convert-hex-to-decimal/#comment-65083
Tue, 13 Apr 2010 07:49:41 +0000http://blog.sqlauthority.com/?p=7785#comment-65083There is no ‘hex’ data type in sql server that we would convert from or to. We have hex literal (eg. 0xFFFF) which is int data type. We can have hex digits stored as ascii characters in varchar (e.g. ‘FFFF’, bytes 70,70,70,70 ), or as binary digits in varbinary (bytes 255, 255). You did not specify which sql type did you really want to convert from. If you want to convert from hex string to integer number, here it is:

]]>By: James Curranhttp://blog.sqlauthority.com/2010/02/01/sql-server-question-how-to-convert-hex-to-decimal/#comment-62430
Mon, 08 Mar 2010 16:16:23 +0000http://blog.sqlauthority.com/?p=7785#comment-62430OK, I’m going to assume that we are starting with a string holding hex digits, and we wnat to convert that into a int.

declare @hex varchar(10);
set @hex = ‘BD12′;

declare @retval int;
set @reval = — 48402 via some magic.

Now, this being the wacky world of SQL, the best solution is gonna be something involving a set operation on tables, so let’s start with a permanaent table mapping hex digits to their values:

The trick here will be the convert the original string (“BD12″, in this example), into the rows if the HexString table. I don’t know of a good way to do that, but I’m sure someone knows a simple way to do that.

]]>By: Razvan Socolhttp://blog.sqlauthority.com/2010/02/01/sql-server-question-how-to-convert-hex-to-decimal/#comment-60536
Tue, 02 Feb 2010 06:42:44 +0000http://blog.sqlauthority.com/?p=7785#comment-60536If you are using SQL Server 2008, you can simply use the built-in function CONVERT, with the style 1:

]]>By: Feodorhttp://blog.sqlauthority.com/2010/02/01/sql-server-question-how-to-convert-hex-to-decimal/#comment-59977
Wed, 20 Jan 2010 06:56:35 +0000http://blog.sqlauthority.com/?p=7785#comment-59977Here is one way to do it: