"Ken1" <ke**@tjohoo.se> wrote in message
news:3b**************************@posting.google.c om...
| 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
