By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,196 Members | 973 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,196 IT Pros & Developers. It's quick & easy.

Traling spaces in result?

P: n/a
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
Share this Question
Share on Google+
1 Reply


P: n/a

"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.