November 11, 2010 at 12:58 am
Hi All,
i have a table that has information regarding version number for particular units.
now problem is this , i have information stored in decimal or int.
i want to convert this into HexaDecimal.
currently i have to import csv in Excel and then using built-in function hex2dec, i am getting my required information.
please tell me do we have any builtin function in TSQL...
or any other method...
November 11, 2010 at 1:22 am
SQL Server doesn't have a built-in function.
Refer to http://support.microsoft.com/kb/104829 for solutions.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
November 11, 2010 at 4:26 am
thank you very much bekker
November 11, 2010 at 8:06 am
actually there is such a builtin function: master.sys.fn_varbintohexsubstring()
I believe it will work fine for you... you would first cast your integer or decimal data to varbinary then hand it to this function.
The probability of survival is inversely proportional to the angle of arrival.
November 14, 2010 at 3:36 am
casting binary or varbinary can be unreliable depending on the collation, you would probably want to use sys.fn_varbintohexstr
The best method is detailed at this link and converts to all bases
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 14, 2010 at 12:40 pm
Henrico Bekker (11/11/2010)
SQL Server doesn't have a built-in function.Refer to http://support.microsoft.com/kb/104829 for solutions.
That article only applies to SQL Server versions up to 7.0 SP1!
November 14, 2010 at 12:54 pm
sturner (11/11/2010)
actually there is such a builtin function: master.sys.fn_varbintohexsubstring()I believe it will work fine for you... you would first cast your integer or decimal data to varbinary then hand it to this function.
There are two reasons to avoid to using that (or the more usual sys.fn_varbintohexstr function). First, they are undocumented (and therefore unsupported). Second, they are scalar T-SQL functions, and do not perform well.
November 14, 2010 at 1:36 pm
Paul White NZ (11/14/2010)
sturner (11/11/2010)
actually there is such a builtin function: master.sys.fn_varbintohexsubstring()I believe it will work fine for you... you would first cast your integer or decimal data to varbinary then hand it to this function.
There are two reasons to avoid to using that (or the more usual sys.fn_varbintohexstr function). First, they are undocumented (and therefore unsupported). Second, they are scalar T-SQL functions, and do not perform well.
To add to what Paul has alread stated, there's a third reason, as well. They don't produce the "correct" (as in "expected") answer for anything that bleeds over into the BIGINT world.
For example... first number below is the max limit on INT. 2nd number is just one more and the whole byte-slicing thing with BIGINT kicks into gear. Run the code and see what happens...
SELECT sys.fn_varbintohexsubstring(0,2147483647,1,0)
SELECT sys.fn_varbintohexsubstring(0,2147483648,1,0)
--Jeff Moden
Change is inevitable... Change for the better is not.
November 14, 2010 at 1:43 pm
Perry Whittle (11/14/2010)
casting binary or varbinary can be unreliable depending on the collation, you would probably want to use sys.fn_varbintohexstr
How is casting binary or varbinary dependent on collation?
How does sys.fn_varbintohexstr help?
The best method is detailed at this link and converts to all bases
That's a very flexible-looking solution (though still a slow T-SQL scalar function) if you need to convert to an arbitrary base, but the requirement here is to convert integers to hex and back again.
SQL Server 2008 enhanced the CONVERT function to make this very easy.
Example:
-- =========================
-- From BIGINT to HEX STRING
-- =========================
DECLARE @Value BIGINT = 9223372036854775807;
-- Using (2008-onward) style 1 gives 0x7FFFFFFFFFFFFF
SELECT CONVERT(CHAR(16), CONVERT(BINARY(8), @Value), 1);
-- Using (2008-onward) style 2 gives 7FFFFFFFFFFFFF (no 0x prefix)
SELECT CONVERT(CHAR(16), CONVERT(BINARY(8), @Value), 2);
-- =========================
-- From HEX STRING to BIGINT
-- =========================
-- Using style 1 (string has 0x prefix)
SELECT CONVERT(BIGINT, CONVERT(BINARY(8), '0x7FFFFFFFFFFFFFFF', 1));
-- Using style 2 (string has no prefix)
SELECT CONVERT(BIGINT, CONVERT(BINARY(8), '7FFFFFFFFFFFFFFF', 2));
Example in-line functions:
CREATE FUNCTION dbo.BigIntToHex
(
@Value BIGINT
)
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN
SELECT hex = CONVERT(CHAR(18), CONVERT(BINARY(8), @Value), 1);
GO
CREATE FUNCTION dbo.HexToBigInt
(
@Value CHAR(18)
)
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN
SELECT value = CONVERT(BIGINT, CONVERT(BINARY(8), @Value, 1));
GO
SELECT F.hex
FROM dbo.BigIntToHex(9223372036854775807) F;
SELECT F.value
FROM dbo.HexToBigInt('0x7FFFFFFFFFFFFFFF') F;
November 14, 2010 at 2:17 pm
Paul White NZ (11/14/2010)
How is casting binary or varbinary dependent on collation?
Character set conversions! In fact Unicode and instance version may have a bigger effect, I may well be wrong on the collation part. It's all detailed here
Paul White NZ (11/14/2010)
How does sys.fn_varbintohexstr help?
My reference was between the function above and the previously mentioned sys.Fn_VarBinToHexSubstring which requires more parameters and as you point out they are undocumented. The latter may produce more issues for the user if they are not aware what the parameters relate too.
Paul White NZ (11/14/2010)
That's a very flexible-looking solution (though still a slow T-SQL scalar function) if you need to convert to an arbitrary base, but the requirement here is to convert integers to hex and back again.SQL Server 2008 enhanced the CONVERT function to make this very easy.
Example:
-- =========================
-- From BIGINT to HEX STRING
-- =========================
DECLARE @Value BIGINT = 9223372036854775807;
-- Using (2008-onward) style 1 gives 0x7FFFFFFFFFFFFF
SELECT CONVERT(CHAR(16), CONVERT(BINARY(8), @Value), 1);
-- Using (2008-onward) style 2 gives 7FFFFFFFFFFFFF (no 0x prefix)
SELECT CONVERT(CHAR(16), CONVERT(BINARY(8), @Value), 2);
-- =========================
-- From HEX STRING to BIGINT
-- =========================
-- Using style 1 (string has 0x prefix)
SELECT CONVERT(BIGINT, CONVERT(BINARY(8), '0x7FFFFFFFFFFFFFFF', 1));
-- Using style 2 (string has no prefix)
SELECT CONVERT(BIGINT, CONVERT(BINARY(8), '7FFFFFFFFFFFFFFF', 2));
Example in-line functions:
CREATE FUNCTION dbo.BigIntToHex
(
@Value BIGINT
)
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN
SELECT hex = CONVERT(CHAR(18), CONVERT(BINARY(8), @Value), 1);
GO
CREATE FUNCTION dbo.HexToBigInt
(
@Value CHAR(18)
)
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN
SELECT value = CONVERT(BIGINT, CONVERT(BINARY(8), @Value, 1));
GO
SELECT F.hex
FROM dbo.BigIntToHex(9223372036854775807) F;
SELECT F.value
FROM dbo.HexToBigInt('0x7FFFFFFFFFFFFFFF') F;
Thank you for your example Paul
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 16, 2010 at 2:58 pm
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply