Hexadecimal value to Decimal value

Question

Hexadecimal value to Decimal value

mj.richardson

Old Hand

Points: 382
More actions
May 8, 2011 at 4:40 pm

#389474

Hello all,
Please be gentle this is my first post I am open to any improvements.
I am trying to do a select statement that will give me the converted value of a column into a new column.
Ex.
NameFileName
Huckfinn46191
SawyerMickeymouse
Jill64191
The results I would need to return would be the following
Name Filename ConvertedValue
Huckfinn 46191 287121
Sawyer MickeyMouse 3310
Jill 64191 410001
I have tried to do the following:
SELECT CONVERT(INT, filename) as decimalp from Change_to_Hex
decimalp
-----------
46191
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'Mickeymouse' to data type int.
select Cast(Cast(NewID() as nvarchar(255)) As nvarchar(20)) from Change_to_Hex
--------------------
3C87F645-A235-47A0-B
5DEB0FB2-19DB-4BFE-B
89F3FF8A-62CD-4C3C-9
(3 row(s) affected)
Please help.
The datatypes in the table are varchar(50)
To sum up this table is built from an SSIS import that grabs the filename and the contents of the file but I then need to get the decimal value of the filename.

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 1

You've actually not given us much to go on. How do you figure that "Sawyer Mickeymouse" converts to 3310₁₀ and that "Jill 64191" converts to 410001₁₀?

I'll tell you right now that the sum of the ASCII values for "Sawyer Mickeymouse" is only 1830₁₀ and a simple XOR Checksum comes out to -145821201₁₀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mj.richardson Old Hand Points: 382 More actions · Answer 2

You've actually not given us much to go on. How do you figure that "Sawyer Mickeymouse" converts to 331010 and that "Jill 64191" converts to 41000110?

I'll tell you right now that the sum of the ASCII values for "Sawyer Mickeymouse" is only 183010 and a simple XOR Checksum comes out to -14582120110

--Jeff Moden

--------------------------------------------------------------------------------

Pardon if I did not explain this correctly, The result that I am looking to get is if I place this value Mickeymouse and/or 64191 into a Hex to decimal converter the end result is what I had typed in.

Is there a why to do this same type of conversion in Sql as the Converter does on the scientific calculators and webpages?

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 3

mj.richardson (5/8/2011)
You've actually not given us much to go on. How do you figure that "Sawyer Mickeymouse" converts to 331010 and that "Jill 64191" converts to 41000110?
I'll tell you right now that the sum of the ASCII values for "Sawyer Mickeymouse" is only 183010 and a simple XOR Checksum comes out to -14582120110
--Jeff Moden
--------------------------------------------------------------------------------
Pardon if I did not explain this correctly, The result that I am looking to get is if I place this value Mickeymouse and/or 64191 into a Hex to decimal converter the end result is what I had typed in.
Is there a why to do this same type of conversion in Sql as the Converter does on the scientific calculators and webpages?

You're not explaining the process behind the scenes. I did convert every character to a hex value, converted that hex value to a decimal value, and added all the decimal values together. Obviously, I'm not doing what you want and you need to explain what a hex converter is going to do to the word "MickeyMouse".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mj.richardson Old Hand Points: 382 More actions · Answer 4

Ok let me ask this way. If I take a scientific calculator and select hex type in 64191 then select dec it gives me the value 410001. The values in the above example would be already in hex form but now I have to translate or convert them to decimal values.

Also if I use any website that allows to do conversion calcs ex http://www.easycalculation.com/hex-converter.php it will give the same results. In sql server 2008 I need to write a script that will convert the hex values in a column to decimal in another column.

If I am not describing correctly I am open to suggestions. I really need help on this.

Lowell SSC Guru Points: 323546 More actions · Answer 5

i don't get 287121 from the conversion.

/*--Results

0x4100014259841

*/

select 0x410001,CONVERT(INT,0x410001)

the problem is without the 0x to identify that it is HE, you'd need to know/infer that the 410001 is hex and not decimal

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

mj.richardson Old Hand Points: 382 More actions · Answer 6

This is exactly what I needed. I was able to take your example and then use it as the following:

select 0x64191,CONVERT(INT,0x64191)

Hexvalue Newdecimal

0x064191 410001

I tried the above but as you stated without the 0x in front of the Hexvalue it just returned 64191 and not the end result I was looking for.

Thank you much on this Lowell and thank you Jeff for hanging in there with me.:-D

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 7

Ouch... that's what I was afraid of. I strongly suspect such a conversion of the hex representation of "MickeyMouse" will be pretty tough even on a BIGINT.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)