Written by Thomas Harlan, Jim McGrath; Reporting Services Team - iatricSystems
From time to time in your work in SQL from the DR, you’ll wind up CAST-ing or CONVERT-ing a number into a string. If that number starts as a DECIMAL or NUMERIC with a scale (the number of places of accuracy to the right of the decimal point) then you will wind up with something like this:
Given you have a NUMERIC(18,8) field, with a value stored in it:
You get, from SQL:
Our scale is respected, and shown to us by the engine.
In the same way if you convert that value to a string:
You get:
As well. All good. As you are processing data, and needing to send it out to other systems, or load it from other systems – then sometimes the extra trailing zeroes (or even leading zeroes, if you get those) can become problematic. And then you need to get rid of them.
Enter the subject of this tip! We’ve written a function: IatricSigDigitsFn()
The consolidates the logic to strip out the leading and trailing zeroes, if they are not significant digits, so that you can then take the string and do something else with it.
There is an optional parameter to leave a leading zero, like: 0.1 in place if you need it. A small thing, perhaps, but handy in a pinch!
And…
As ever, if you need help with Meditech DR index creation, reporting, extract or analysis please feel free to give your Iatric Systems Sales Representative a call!
Our Report Writing team can help with Data Repository reporting, NPR report writing, data extract or analysis, and much more. Simply reach out to your Iatric Systems Account Executive or email info@iatric.com to discuss how we can help support your team!
