How to supress leading Zeros

[Shree0410]

I have a column of lenth 10 which could possible have leading zeroes.

How can I supress the leading zeros. I worked this out by checked byte by byte accross the 10 bytes, but us there is a masking function that I can use?

Thanks for the help

[chulett]

You could define it as a numeric datatype, which would automagically do that for you, or you could check out the FMT function.

[DSguru2B]

Multiply it by 1
An arithmatic manipulation on a character forces it to become an integer (implicit conversion).
Another way

[kcbland]

If you try math on a NULL value, you'll get an Abnormal Termination. Consider any of these options:

Code: Select all

If ISNULL(link.column) OR NOT(NUM(link.column)) Then 0 Else link.column * 1

Code: Select all

If ISNULL(link.column) OR NOT(NUM(link.column)) Then 0 Else link.column + 0

To enforce a set number of decimal places with rounding:

Code: Select all

If ISNULL(link.column) OR NOT(NUM(link.column)) Then 0 Else OCONV(link.column, "MDxy")

where x is the number of decimals remaining after the y decimal shift

[DSguru2B]

Thanks Ken for the input, completely overlooked that

[cconley]

Using the Trim function with the "L" option will also trim leading zeros: Trim(field,'0','L')

[michaeld]

This will do it:

DecimalToString(ColumnName),"fix_zero,suppress_zero")

[ray.wurlod]

Not in a server job, it won't!