W MS SQL Server, a dokładnie w języku T-SQL występują pewne ograniczenia podczas konwersji typu hexadecimal na układ dziesiętny. Poniżej przedstawię rozwiązanie problemu.
Do konwersji typów varbinaty (liczby przedstawione w układzie szestnastkowym) na układ dziesiętny służą dwie funkcje T-SQL. Są nimi CAST() oraz CONVERT(). W tym przypadku lepiej będzie używać funkcji CONVERT().
Składnia:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
data_type [ ( length ) ] – typ danych na który będzie konwertowane wyrażenie.
length – Opcjonalnie długość dla wybranych typów danych
expression – konwertowane wyrażenie
style – opcjonalny styl konwersji.
W przypadku typów varbinary są dostępne trzy style:
SELECT CONVERT(VARBINARY(16), 'A1',0) SELECT CONVERT(VARBINARY(16), '0xA1',1) SELECT CONVERT(VARBINARY(16), 'A1',2)
Style:
0 – uzyskuje się w wyniku kody ASCII z podanego wyrażenia. W tym przypadku wynik to 0x4131 czyli 0x jako opis wyrażenia binarnego, 41 jako kod ASCII ‘A’, oraz 31 jako kod ASCII ‘1’
1 – w wyniku uzyskuje się kod binarny o wartości 0xA1. Należy jednak pamiętać, że dwa pierwsze znaki poddawane do konwersji muszą mieć wartość ‘0x’.
2 – – w wyniku uzyskuje się kod binarny o wartości 0xA1. Należy jednak pamiętać, że musi to być typowa wartość bez dwóch pierwszych znaków ‘0x’
Jeżeli chcemy dokonać konwersji varbinary na liczę dziesiętną możemy posłużyć się prostym przykładem.
SELECT CONVERT(VARBINARY(16), 'A1',2) -- A->16*10 +1 = 161 SELECT CONVERT(INT, CONVERT (VARBINARY(16), 'A1',2)) -- >161
W powyższym przykładzie dokonujemy konwersji wartości binarnej A1 na układ dziesiętny. Wynik jest poprawny czyli 161 ( z układu szesnastkowego A = 16*10, oraz 1 co daje 161. Poniżej próbujemy dokonać konwersji większej wartości A000000F na typ INT. Otrzymany wynik jest wartością ujemną . Jest to spowodowane tym, że został przekroczony górny zakres INT czyli 2,147,483,647 (INT- od -2^31 (-2,147,483,648) do 2^31 – 1 (2,147,483,647)).
SELECT CONVERT(INT,convert(VARBINARY(16), 'A000000F',2)) -- >-1610612721
Najprościej jest zmienić typ na BIGINT. W wyniku otrzymujemy wtedy prawidłową liczbę.
SELECT CONVERT(BIGINT,convert(VARBINARY(16), 'A000000F',2)) -- > 2684354575
Kolejny przykład to konwersja jeszcze większej wartości binarnej A000000FFFFFFFFF, której to wartość przekracza zakres dodatniego zakresu BIGINT (od -2^63 (-9,223,372,036,854,775,808) do 2^63-1 (9,223,372,036,854,775,807))
SELECT CONVERT(BIGINT,convert(VARBINARY(16), 'A000000FFFFFFFFF',2)) -- >-6917528958921605121
Wynik konwersji to liczba z ujemnego zakresu BIGINT. Można otrzymany wynik w prosty sposób przeliczyć i przekonwertować do typu Numeric(38,0).
SELECT CONVERT(NUMERIC(38,0), CASE WHEN CONVERT(BIGINT ,CONVERT(VARBINARY(16), 'A000000FFFFFFFFF',2)) < 0 THEN ((-9223372036854775808 - CONVERT(BIGINT ,CONVERT(VARBINARY(16), 'A000000FFFFFFFFF',2)) -1) * (-1)) + 9223372036854775807 ELSE CONVERT(BIGINT ,CONVERT(VARBINARY(16), 'A000000FFFFFFFFF',2)) END ) -- > 11529215114787946495
W związku z konwersją na typ Numeric/Decimal nasuwa się myśl czemu od razu nie zastosować konwersji na ten typ z Varbinary. Występują błędy podczas konwersji.
SELECT CONVERT(DECIMAL(21,0),convert(VARBINARY(8), 'A000000F',2)) -- > ERROR SELECT CONVERT(DECIMAL(21,0),convert(VARBINARY(8), 11111119109,2)) -- > OK SELECT CONVERT(DECIMAL(21,0),convert(VARBINARY(8), 111119109,2)) -- > ERROR
W tym przypadku uzyskuje się błąd:
Msg 8114, Level 16, State 5, Line 19 Error converting data type varbinary to numeric.
Dlatego najlepszym rozwiązaniem jest używanie wartości binarnych zgodnych z typem VARBINARY(16) lub też można utworzyć funkcję, w której to zostanie wartość binarna zamieniona na varchar, podzielona na odpowiednie fragmenty, których wartości zostaną najpierw przekonwertowane na typ BIGINT, a następnie na numeric.
/************************************************************
* Code created & formatted by Dariusz Brejnak © DBSoft 2013
* Time: 2013-12-22 12:09:34
* File: sp_HEX120_DEC.sql
************************************************************/
CREATE FUNCTION sp_HEX120_DEC
( @hex VARBINARY(30)
)
RETURNS NUMERIC(38 ,0)
AS
BEGIN
DECLARE @varhex VARCHAR(30) = RIGHT(REPLICATE('0' ,30) +
CONVERT(VARCHAR(30) ,@hex ,2) ,30)
DECLARE @x1 NUMERIC(38 ,0)
DECLARE @x2 NUMERIC(38 ,0)
DECLARE @x3 NUMERIC(38 ,0)
DECLARE @xseg NUMERIC(38 ,0) = CAST(
CAST(CONVERT(VARBINARY(16) ,'FFFFFFFFFFFFFF' ,2) AS BIGINT) AS
NUMERIC
)
DECLARE @xseg2 NUMERIC(38 ,0) =
CAST(5192296858534827628530496329220096 AS NUMERIC(38 ,0))
-- x3 x2 x1
-- 2 14 14
-- 0x 87 E943E5F143A422 2A1F6554EDF372
SET @x1 = CAST(
CAST(CONVERT(VARBINARY ,RIGHT(@varhex ,14) ,2) AS BIGINT) AS NUMERIC
)
SET @x2 = CAST(
CAST(
CONVERT(VARBINARY ,LEFT(RIGHT(@varhex ,28) ,14) ,2) AS BIGINT
) AS NUMERIC
)
SET @x3 = CAST(
CAST(
CONVERT(VARBINARY ,LEFT(RIGHT(@varhex ,30) ,2) ,2) AS BIGINT
) AS NUMERIC
)
RETURN ((@x1 + (@x2 * @xseg) + @x2) + (@x3 * @xseg2))
END
Tak przedstawiona funkcja jest wstanie przekonwertować wartości binarne o maksymalnej długości 120 bitów. Uzyskuje się wtedy wartości numeryczne o precyzji 37 z maksymalnej precyzji 38 dla wartości numerycznych.
Linki:
