Oracle SQL TO_CHAR variable length

Question

I'd like to ask you with help with a TO_CHAR method in Oracle SQL.

TO_CHAR(Number_parameter,'9999D99','NLS_NUMERIC_CHARACTERS = ''. ''') vc_num,

Number_Parameter is coming in as a decimal number with usual values between 10.10 to 1999.99 (but not limited to them) I need to create a VARCHAR with the smallest representation of the value with at most 2 decimal points. My problem is that I have not found a mask that would satisfy my needs.

I need 200.99 as '200.99' but 10.1 as '10.1' while now I am getting ' 10.1 ' (with the white spaces, which I do not want) also 150 should translate to '150' and not '150.00'

Can you help me please?

Use the `FM` modifier for your format mask – Jul 26 '16 at 12:14 — , Jul 26 '16 at 12:14

score 2 · Accepted Answer · answered Jul 26 '16 at 12:19

As @a_horse_with_no_name suggested, using the FM format modifier will get rid of the leading space, and will also remove trailing zeros. But it's not quite there:

with t(number_parameter) as (
  select 200.99 from dual
  union all select 10.10 from dual
  union all select 150.00 from dual
)
select to_char(number_parameter,'FM9999D99','NLS_NUMERIC_CHARACTERS = ''. ''') vc_num
from t;

VC_NUM 
--------
200.99  
10.1    
150.

To get rid fo the trailing period you'll need to trim that:

with t(number_parameter) as (
  select 200.99 from dual
  union all select 10.10 from dual
  union all select 150.00 from dual
)
select rtrim(
  to_char(number_parameter,'FM9999D99','NLS_NUMERIC_CHARACTERS = ''. '''),
  '.') vc_num
from t;

VC_NUM 
--------
200.99  
10.1    
150

Or as you discovered yourself, the TM text minimum format model also works for your data:

with t(number_parameter) as (
  select 200.99 from dual
  union all select 10.10 from dual
  union all select 150.00 from dual
)
select to_char(number_parameter,'TM') vc_num
from t;

VC_NUM                                                         
----------------------------------------------------------------
200.99                                                          
10.1                                                            
150

Oracle SQL TO_CHAR variable length

1 Answers1