Convert hexadecimal string to decimal

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
bb98
Participant
Posts: 4
Joined: Sun Apr 02, 2017 7:54 am

Convert hexadecimal string to decimal

Post by bb98 »

We are looking for a solution to convert a hexadecimal string to a decimal within Datastage. The source system provides an 8 character string.
Input: 01FEE741 Output: 33.4825610
Input: FAD4F96B Output: -86.7058160

We have used SQL Server functions to convert the string but would like to eliminate the need to load the data and then re-process it. We would like to perform the conversion on the initial load within a transformation stage.

The SQL server function performs the following:
CAST(CAST(Convert(VARBINARY, @hexstr, 2) AS INT)/1000000.0 AS DECIMAL(18,7))

How can we duplicate this in data stage?
qt_ky
Premium Member
Premium Member
Posts: 2895
Joined: Wed Aug 03, 2011 6:16 am
Location: USA

Post by qt_ky »

In a Parallel job:

Use the Conversion() function to convert an input string from hexadecimal to decimal with conversion code MX and conversion mode I.

Code: Select all

Conversion(link.column, 'MX', 'I')
See the Knowledge Center online or the Parallel Job Developer's Guide Appendix B. Parallel transform functions for more details.

I believe this function was newly added as of version 11.3.x. I've tested it in 11.3 and it appears to work. My first result matches yours anyway; the second one does not output a negative number however. Perhaps there's more logic involved.

Or if you want to go with a Server job or a Parallel job with BASIC Transformer then there are other functions also available to convert hex that have always been in the product.
Choose a job you love, and you will never have to work a day in your life. - Confucius
ArndW
Participant
Posts: 16318
Joined: Tue Nov 16, 2004 9:08 am
Location: Germany
Contact:

Post by ArndW »

If you wish to use a Server Job or stage, the conversion

Code: Select all

OCONV(column.name,"MCX")
will produce the Output you want (without the implicit decimal, you'll have to do that yourself)
Post Reply