fn_varbintohexstr (decimal to hexadecimal) - help get expected output 1

[Olavxxx]

Hi,

In oracle, there is a function:
dec2hex().

If I feed it with the integer value: 5044857

select dec2hex(5044857) from dual;

It returns the value: 304MX

In SQL (SQL 2008 I believe it is), there are two ways I have tried doing the same:

SELECT 5044857, master.dbo.fn_varbintohexstr(5044857)
SELECT 5044857, CONVERT(VARBINARY(8), 5044857)

They both return: 0x004CFA79

How can I geet the expected: 304MX ?

Olav Alexander Mjelde

 

[bborissov]

How in HEX format you can have M or X when the values are from
0 to F?

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[Olavxxx]

Hi, not sure how oracle does it.
I believe it does two conversions and what it returns is chr.

I have looked at the ascii conversion tables:

Expected(chr)	3	4	4	M	X
HEX	        33	30	34	4D	58
Dec	        51	52	52	77	88
Oct	        063	064	064	115	130

I need the Expected (chr), from: 5044857 .
The hex alone gives me the value: 0x004CFA79

If I do a reverse hex from the value: 304MX (expected), it gives me hex: 33 30 34 4d 58, which is correct vs the table above. Though I dont know how to get from 5044857 to 344MX.

Olav Alexander Mjelde

 

[starsky51]

I don't use Oracle, but according to

http://www.orafaq.com/wiki/Hexadecimal

, dec2hex returns a hex value, not ASCII. Are you using a custom function?
I can't see how any of the values in your conversion table relate to the value 5044857.

 

[Olavxxx]

Hi again,
I found the founction in the oracle database, it looks identical to the one on this page:

http://www.orafaq.com/wiki/Hexadecimal

Exception:
It uses MOD36 instead of MOD16, also the N2 := trunc( N2 / 16 ); uses /36 instead of /16.

So it seems to loop the characters one by one, doing mod36 and checking if that is > 9 or < 9. If >9, it does a chr conversion.

However I just have a read user for the mssql db.... Maybe I have to make this in my c# application instead.



Olav Alexander Mjelde

 

[starsky51]

Hmm.. Base 36? That's a new one by me. The following function should provide what you need. I've transcoded (is that a word?) it from the orafaq page and switched the '/16' bits with '/ 36':

CREATE FUNCTION dbo.ufnDecToBase36 (@val int)
RETURNS varchar(MAX)
AS 
BEGIN
    DECLARE @hexval varchar(64) = ''
      , @N2 int = @val
      , @digit int
      , @hexdigit char = ''

    WHILE (@N2 > 0) 
        BEGIN
            SET @digit = @N2 % 36
            IF @digit > 9 
                BEGIN
                    SET @hexdigit = CHAR((ASCII('A') + @digit - 10))
                END
            ELSE 
                BEGIN
                    SET @hexdigit = CAST(@digit AS char)
                END
            SET @hexval = @hexval + @hexdigit
            SET @N2 = @N2 / 36
        END
    RETURN REVERSE(@hexval)
END

SELECT  dbo.ufnDecToBase36(5044857)

 

[Olavxxx]

Thank you.. I have also found this code:

http://www.stum.de/2008/10/20/base36-encoderdecoder-in-c/

which I have tested to work in my C# application

Olav Alexander Mjelde