How to use Left Mid Right and hex-to-dec conversion in SQL

Question

How to use Left Mid Right and hex-to-dec conversion in SQL

Farrell Thompson

Grasshopper

Points: 18
More actions
May 13, 2005 at 1:27 pm

#164393

Please have pity on a newbie flying 'without a net'...
I have one table with 2 fields, but the data is pretty dynamic. I want to re-populate a second table with both fields from table 1, AND a 'translation' of one of the fields that'll end up as a text field.
TBL1.hexnumber (10 digits)
TBL1.refnumber
TBL2.hexnumber (copy inserted)
TBL2.refnumber (copy inserted)
TBL2.translation will be the toughie... source is TBL1.hexnumber (I need to parse the hexadecimal number into discrete chunks like this: [(left hexnumber,2)(mid hexnumber, 3, 1)(mid hexnumber, 4, 1)(right hexnumber,6)], THEN convert each of those chunks from hex to decimal, THEN concatenate the result into a text field or numerical field allowing for leading zeroes.
Any ideas? I'm under a lot of pressure here, and sometimes small towns don't really have the deepest talent pools to pull from!

EXAMPLE:
HEX# (L,M,M,RT,H2D TRANS, CONCAT)
D2fb00fe36 D2 f b 00fe36 052 210 15 11 00065078 011210151100065078

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

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

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

Not trying to be difficult here... hex doesn't normally work quite the way you described so could you post an example of the hex number and what the expected outcome would be? Thanks...

--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)

Farrell Thompson Grasshopper Points: 18 More actions · Answer 2

ok, what I meant to say was...

D2fb00fe36

D2

f

b

00fe36

052

210

15

11

00065078

052210151100065078

the 10 digit hex# becomes an 18 digit decimal number, result always bears prefix of '052' as in example, then the translation from hex to dec takes over for the remaining 15 digits of the result.

so: (052)+(hex2dec (d2)=210)+(hex2dec (f)=15)+(hex2dec (b)=11)

+ (hex2dec(00fe36)=00065078)

Due to the software in use, I am constrained within these patterns and must format each result with the appropriate # of digits (leading zeros ok)