Re: Traling spaces in result?

"Ken1" <ken1_at_tjohoo.se> wrote in message news:3bb6578e.0402170526.7f3cc1e1_at_posting.google.com...
| Why is it that rows returned from this statement have extra trailing
| spaces?
| I.e. today tuesday the returned string is "Tuesday " with the length
| 9.
|
| SELECT TO_CHAR(SYSDATE, 'Day','nls_date_language=english'),
| LENGTH(TO_CHAR(SYSDATE, 'Day','nls_date_language=english')) FROM dual
|
| I am using oracle 8.1.7
|
| I read somewhere on the internet that FM (Format Models?) could
| solve this problem I have, but I thought that the 'Day' was the
| actual FM.
|
| I have temporary solved it using trim() but if I do a compare
| between this and a column in the database I don't want to use trim()
| everywhere.
|
| Any other good solution.
|
| /Ken1

default behavior for formats is for variable length strings to be blank padded and variable length numbers to be zero padded, which is often appropriate for tabular results (time elements added to default hire dates, view with a fixed font to line up results):

SQL> select ename, to_char(hiredate, 'Month dd, yyyy hh24:mi') as hired   2 from emp
  3 /
...

ENAME HIRED

---------- ------------------------
SMITH      December  17, 1980 00:01
ALLEN      February  20, 2001 00:02
WARD       February  22, 1981 00:03
JONES      April     02, 1981 00:04
MARTIN     September 28, 2001 00:05
CLARK      June      09, 1981 00:06
SCOTT      December  09, 1982 00:07
KING       November  17, 1981 00:08
TURNER     September 08, 1981 00:09
Adams      January   12, 1983 00:10
JAMES      December  03, 1981 00:11
FORD       December  03, 1981 00:12
MILLER     January   23, 1982 00:13

'FM' (fill mode?) is a TOGGLE to turn padding on/off -- it is often used multiple times within a format, as shown below:

used once, turns off all padding (including in number elements):

SQL> select
  2 ename
  3 , to_char(hiredate, 'fmMonth dd, yyyy hh24:mi') as hired   4 from emp
  5 /
...

ENAME HIRED

---------- ------------------------
SMITH      December 17, 1980 0:1
ALLEN      February 20, 2001 0:2
WARD       February 22, 1981 0:3
...
JAMES      December 3, 1981 0:11
FORD       December 3, 1981 0:12

used multiple times, so that the time element looks better (also switched over to 12-hour time with the meridian indicator on this one)

SQL> select
  2 ename
  3 , to_char(hiredate, 'fmMonth dd, yyyy hh:fmmi am') as hired   4 from emp
  5 /
...

ENAME HIRED

---------- ---------------------------
SMITH      December 17, 1980 12:01 am
ALLEN      February 20, 2001 12:02 am
...
JAMES      December 3, 1981 12:11 am
FORD       December 3, 1981 12:12 am

another couple multiple-use example:

SQL> select
  2 ename
  3 , to_char(hiredate, 'fmDay mm/fmdd/yyyy fmhh:fmmi:ss am') as hired   4 from emp
  5 /
...

ENAME HIRED

---------- --------------------------------
SMITH      Wednesday 12/17/1980 12:01:00 am
ALLEN      Tuesday 2/20/2001 12:02:00 am
...
JAMES      Thursday 12/03/1981 12:11:00 am
FORD       Thursday 12/03/1981 12:12:00 am

(view this one in a fixed-font):

SQL> select
  2 ename
  3 , to_char(hiredate, 'Day fmmm/fmdd/yyyy fmhh:fmmi:ss am') as hired   4 from emp
  5 /
...

ENAME HIRED

---------- --------------------------------
SMITH      Wednesday 12/17/1980 12:01:00 am
ALLEN      Tuesday   2/20/2001 12:02:00 am

• mcs