CODEWITHSUNDEEP
sqldatabase
Deepak
Deepak

Reputation:

How to extract a 0 before '.'

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?

Upvotes: 0

Views: 156

Answers (2)

Jeff Olson
Jeff Olson

Reputation: 329

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.

Upvotes: 3

Alnitak
Alnitak

Reputation: 339916

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.

Upvotes: 3

Related Questions