May 27, 2008 at 9:28 am
Hello all:
I am fairly new to MSSQL features and functions. I have been working with Oracle for over 5 years. My newest project requires MSSQL so I am trying to right a solution for a client.
There are several function that exist as "Add-Ins" under "Analysis ToolPak" in Excel that do not exist in SQL Server.
There also does not seem to be any possiblity to create them in SQL Server as a user-defined function.
More specifically I am trying to extract from a hexadecimal source part of the its data.
Then convert the data to its binary root form and extract part of this data.
Then convert the binary into a decimal answer.
RAW HEX DATA:
D2:
0130323530333739343234310000ceae01420a5ab642b71562000000
E2:
=MID(D2,29,8)
RESULT:ceae0142
F2:
=MID(D2,37,8)
RESULT:0a5ab642
G2:
=CONCATENATE(MID(E2,7,2),MID(E2,5,2),MID(E2,3,2),MID(E2,1,2))
RESULT:4201aece
H2:
=CONCATENATE(MID(F2,7,2),MID(F2,5,2),MID(F2,3,2),MID(F2,1,2))
RESULT:42b65a0a
Problem Formulas:
I2:
=(IF(BIN2DEC(MID(HEX2BIN(MID(H2,1,2),8),1,1))>0,-1,1))*2^(BIN2DEC(CONCATENATE(MID(HEX2BIN(MID(H2,1,2),8),2,7),MID(HEX2BIN(MID(H2,3,2),8),1,1)))-127)*((BIN2DEC(MID(HEX2BIN(MID(H2,3,2),8),2,7))*2^16+HEX2DEC(MID(H2,5,4)))/2^23+1)
RESULT:32.420708
J2:
=(IF(BIN2DEC(MID(HEX2BIN(MID(H2,1,2),8),1,1))>0,-1,1))*2^(BIN2DEC(CONCATENATE(MID(HEX2BIN(MID(H2,1,2),8),2,7),MID(HEX2BIN(MID(H2,3,2),8),1,1)))-127)*((BIN2DEC(MID(HEX2BIN(MID(H2,3,2),8),2,7))*2^16+HEX2DEC(MID(H2,5,4)))/2^23+1)
RESULT:91.175858
What I am looking for is the ability to convert from one base to another base numbering system freely.
You would think Microsoft would have already addressed this gap in programming between its own products.
Any assistance would be greatly appreciated.
Thanks
Kevin McCullor
May 27, 2008 at 6:10 pm
First, create a Tally table... see the following URL for what it is and how it replaces some loops... take time to read it 'cause it's worth it 😉
http://www.sqlservercentral.com/articles/TSQL/62867/
Next, create a function to convert a string that looks like what MS calls a "binary string" (hex, really) to an actual Binary data type (again, hex really).
CREATE FUNCTION dbo.fHexStringToBinary
/****************************************************************************************
Purpose:
This function will take a string of characters that look like Hexadecimal or Varbinary
(has the "0x" as a prefix) and convert it to an actual Varbinary value. If the input
consist of 8 bytes (2 hex characters each), the result will be suitable for comparison
with TimeStamps.
Notes:
1. Automatically removes all dashes and space including leading or trailing spaces.
2. Returns NULL if odd number of characters is present.
3. Returns NULL if input string contains other non-hex characters other than those
found in Note 1.
4. Returns NULL if the stripped input string contains more than 32 hex characters
Performance:
Approximately 12k rows per second in lots of 12k rows on Pentium 4 1.8Ghz w/IDE
Drives 2GB-RAM
Dependencies:
1. A "Tally" table must be present in the same database as this function. The Tally
table is used to replace a WHILE loop.
Written by Jeff Moden, 03 Jul 2006
****************************************************************************************/
--=======================================================================================
-- Declare the I/O parameters
--=======================================================================================
(
@pHexStr as VARCHAR(50)
)
RETURNS VARBINARY(16)
AS
BEGIN
--=======================================================================================
-- Function body
--=======================================================================================
--===== Remove any dashes/spaces and the '0x' prefix if present
SET @pHexStr = UPPER(
REPLACE(
REPLACE(
REPLACE(
@pHexStr
,'-','')
,' ','')
,'0x','')
)
--===== Declare local variables.
DECLARE @BinResult VARBINARY(8) --Final result
DECLARE @CharCount INT --Number of characters left in @pHexStr
SET @CharCount = LEN(@pHexStr)
--===== If any non-hex characters present or uneven number
-- of characters or too big, early exit with NULL return
IF @pHexStr LIKE '%[^0-9A-F]%'
OR @CharCount%2 > 0
OR @CharCount > 32
RETURN NULL
--=====
SELECT @BinResult = ISNULL(@BinResult,0x)
+ CAST(
(CHARINDEX(SUBSTRING(@pHexStr,(N),1),'123456789ABCDEF'))*16
+
(CHARINDEX(SUBSTRING(@pHexStr,(N)+1,1),'123456789ABCDEF'))
AS BINARY(1))
FROM dbo.Tally WITH (NOLOCK)
WHERE N <= @CharCount
AND N%2=1
ORDER BY N ASC
--=======================================================================================
-- Function end
--=======================================================================================
RETURN @BinResult
END
Last, but not least... use it to solve your problem...
--===== Put the original hex string where we can get at it
DECLARE @D2 VARCHAR(100)
SET @D2 = '0130323530333739343234310000ceae01420a5ab642b71562000000'
--===== Now, pick a couple of pieces of "8" apart and convert to whole numbers
SELECT CAST(dbo.fHexStringToBinary(SUBSTRING(@D2,29,8)) AS BIGINT) AS F2
SELECT CAST(dbo.fHexStringToBinary(SUBSTRING(@D2,37,8)) AS BIGINT) AS G2
So far as MS not having continuity in it's products... I'm real happy that folks find it difficult to do spreadsheets in a database 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply