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?
Convert hexadecimal string to decimal
Moderators: chulett, rschirm, roy
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.
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.
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')
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
If you wish to use a Server Job or stage, the conversion
will produce the Output you want (without the implicit decimal, you'll have to do that yourself)
Code: Select all
OCONV(column.name,"MCX")
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>