Post reply

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

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

    D2fb00fe36D2fb00fe36052210151100065078  011210151100065078

  • May 15, 2005 at 10:09 am

    #558929

    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)

  • May 16, 2005 at 4:48 pm

    #559146

    ok, what I meant to say was...

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

     

     

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

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