471,090 Members | 1,414 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,090 software developers and data experts.

Traling spaces in result?

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
Jul 19 '05 #1
1 3423

"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
Jul 19 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Y. H. | last post: by
5 posts views Thread by Marco Gallo | last post: by
3 posts views Thread by Moshe Foobar | last post: by
12 posts views Thread by Joriveek | last post: by
3 posts views Thread by Matthias Langbein | last post: by
1 post views Thread by Ken1 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.