How to extract a 0 before '.'

Question

select 00.0004 ||' ' || 'USD/MT' from dual

this gives the o/p as

.0004 USD/MT

I also want a 0 before the decimal so that i would get the result as 0.0004 USD/MT

Can anyone help me out?

Answer 1

Your 00.0004 variable is being treated as a float (or double) and is being automatically converted into a string using the default conversion format because of the concatenation operator.

If the variable is really a string, enclose it in quotes.

If it's actually a real value being extracted from a database column, use the appropriate function:

 TO_CHAR(field, '0.9999')

to convert it into an string formatted as required before concatenating it with the other fields.

Answer 2

If it is indeed Oracle - then replace "select 00.0004" with:

select to_char(00.0004,'0.9999')

The to_char function in Oracle as used in this example takes a value as the first parameter, and a format string for the second parameter. The 0 tells the system to preserve this numeric placeholder even when the value is zero.