0

Should be a pretty simple question. I have two fields - one a year field and the other a month field. The month field is an integer and if there is only one digit such as 6 for June there is no leading zero. I want to concatenate the two fields together to get 201406 not 20146 if I concatenate them together now. I tried year||to_char(month,'09') but the field is being displayed as 2014 06 with a space in-between the year and month. Is there a way to do this without a space?

Frank Schmitt
  • 30,195
  • 12
  • 73
  • 107
Luke
  • 31
  • 8
  • use `trim(year)||to_char(month,'09')` thats because your yaer field was saved with a space – Jorge Campos Mar 30 '15 at 13:56
  • Thanks Jorge, that should work but still displaying 2014 06 and the length is 7. Any other suggestions? I am using Oracle 11g is that matters – Luke Mar 30 '15 at 14:00

2 Answers2

1

If your output contains a space, then either your year or your month column contains a space. To get rid of these, you can use TRIM:

with v_data(year, month) as (
  select '2015 ', ' 1' from dual union all
  select ' 2014 ', ' 12 ' from dual union all
  select '2014', '3' from dual
)
select trim(year) || lpad(trim(month), 2, '0') 
from v_data

(this assumes that you really have two string columns - if you indeed have two date columns, please add example input to your question)

UPDATE

If you want to use to_char() instead, you should use the FM format modifier to get rid of the space:

select trim(year) || trim(to_char(month, 'FM09')) 
from v_data
Frank Schmitt
  • 30,195
  • 12
  • 73
  • 107
  • Thanks both answers worked. For some reason I had to trim the formatted filed too...trim(year)||trim(to_char(month,'09')) – Luke Mar 30 '15 at 14:02
  • That's because without the `FM` modifier, many format models of to_char() add a leading blank, see the Oracle docs (link in my updated answer). – Frank Schmitt Mar 30 '15 at 14:05
1

The issue is that, by default, to_char leaves a space in front of a positive formatted number, so that they line up well with negative numbers. To prevent this, use to_char(month,'fm09').

Allan
  • 17,141
  • 4
  • 52
  • 69