LTRIM
Brett Rhoades, September 27, 2003 - 4:32 pm UTC
The question was not understood. I was asking what I needed to do to use the LTRIM with a time function to remove the leading zero.
September 27, 2003 - 9:02 pm UTC
trick question then -- you DON'T NEED ltrim!
you use fm
and if you wanted to use ltrim -- well, it would be pretty easy. You want to ltrim 0 -- there you go
ltrim( string, '0' )
but don't ltrim, that would be wrong, use fm as shown.
LTRIM of time function
Brett Rhoades, September 27, 2003 - 4:39 pm UTC
I did try the adding the fm to the time and here is what occurred. TO_CHAR(C_SEC_TIME, 'fmHH:MI AM') to see what would occur, here is the results:
TO_CHAR(
--------
10:0 AM
9:30 AM
8:0 AM
11:0 AM
2:0 PM
9:0 AM
10:0 AM
8:0 AM
2:0 PM
3:0 PM
8:0 AM
September 27, 2003 - 9:04 pm UTC
ops$tkyte@ORA920LAP> select to_char( x, 'fmhhfm:mi am' ) from t;
TO_CHAR(
--------
9:00 am
fm on
fm off
fm on
fm off
(fm is documented, its behavior can be read about...)
Is this on the manual?
Ringo, September 27, 2003 - 8:17 pm UTC
What a greate tools !
Is it on the oracle manual?
I been writing small routine outside oracle to remove the '0' and a magic word just did it !
September 27, 2003 - 9:08 pm UTC
yes, fm would be "documented" in the sql reference manual as all formats all..
LTRIM of Time Function
Brett Rhoades, September 27, 2003 - 9:27 pm UTC
The FM is great and will be used for other future problems! But not the solution I need. I need to know how to use the LTRIM with a time function so the result will be from 08:00 AM to 8:00 AM. Again, I have tried it in several ways to no avail... This is what I need.
Thanks.
September 27, 2003 - 10:22 pm UTC
select to_char( x, 'fmhhfm:mi am' ) from t;
what the heck is wrong with that????????
but, if for some unfathomable reason, you are "outlawed" from doing it right, you can:
ops$tkyte@ORA920LAP> select to_char( x, 'fmhhfm:mi am' ) from t;
TO_CHAR(
--------
9:00 am
ops$tkyte@ORA920LAP> select ltrim( to_char( x, 'hh:mi am' ),'0') from t;
LTRIM(TO
--------
9:00 am
ltrim is pretty easy to use, you just have to tell it what to ltrim, that's all.
it'll be slower
it'll take long to type
it is up to you
LTRIM for Time Function
Brett Rhoades, September 27, 2003 - 10:47 pm UTC
Thank you very much. I tried the code and this is the result:
SQL> SELECT C_SEC_ID, SEC_NUM, C_SEC_DAY, LTRIM( TO_CHAR(C_SEC_TIME, 'HHFM:MI AM' ), '0')
2 FROM COURSE_SECTION;
C_SEC_ID SEC_NUM C_SEC_DAY LTRIM(TO
---------- ---------- ---------- --------
1 1 MWF 10:0 AM
2 2 TR 9:30 AM
3 3 MWF 8:0 AM
4 1 TR 11:0 AM
5 2 TR 2:0 PM
6 1 MWF 9:0 AM
7 2 MWF 10:0 AM
8 1 TR 8:0 AM
9 1 MWF 2:0 PM
10 2 MWF 3:0 PM
11 1 MTWRF 8:0 AM
I don't mean to be a pain but, if LTRIM strips the zero on the left, why does it also strip the zero on the right? How can I fix that using the LTRIM?
Again I appreciate your patience.
LTRIM of Time Function
Brett Rhoades, September 27, 2003 - 10:59 pm UTC
Thanks. It works!!
SQL> SELECT C_SEC_ID, SEC_NUM, C_SEC_DAY, LTRIM( TO_CHAR(C_SEC_TIME, 'HH:MI AM' ), 0 )
2 FROM COURSE_SECTION;
C_SEC_ID SEC_NUM C_SEC_DAY LTRIM(TO
---------- ---------- ---------- --------
1 1 MWF 10:00 AM
2 2 TR 9:30 AM
3 3 MWF 8:00 AM
4 1 TR 11:00 AM
5 2 TR 2:00 PM
6 1 MWF 9:00 AM
7 2 MWF 10:00 AM
8 1 TR 8:00 AM
9 1 MWF 2:00 PM
10 2 MWF 3:00 PM
11 1 MTWRF 8:00 AM
Homework Assignment?
Jeremy Smith, September 28, 2003 - 4:50 am UTC
Given the example data the person asking the question presented, and their "requirement" that it be using LTRIM, I'm highly suspicious that this was a class homework assignment. I don't know how you feel about those...
September 28, 2003 - 9:33 am UTC
I find the language differences from country to country add additional meaning.
many people don't have questions, they have "doubts". doubts being a negative term in my mind, but not theirs
"reply immd" is used casually, not as a demand even if it sounds like that.
"requirement" is used when "I think i have a need" is more appropriate.
so, I don't assume "homework" all of the time.
I can say, if this was homework and the assignment was "use ltrim to remove a leading zero", school has become trivial since I was there. First, ltrim is very easy to use (fully documented, i was surprised at the question actually, using ltrim is somewhat "easy" here). Second, ltrim is the wrong approach to the question, fm/fm would be the right way to do it.
Thanks for making me know about this today ! - you have lots of patience too
A reader, September 28, 2003 - 12:08 pm UTC
number - number returns -0
A reader, November 03, 2003 - 3:58 am UTC
Hi
I am puzzled why I am getting negative zeros in this query
select
dur_sesion_ip,
FEC_INICIO_IP,
dur_sesion_ip -
(trunc(to_date(FEC_INICIO_IP,'YYYYMMDDHH24MISS') + 1) - to_date(FEC_INICIO_IP,'YYYYMMDDHH24MISS')) * (24*3600) seg_curr_dia
from bvg_idrs_lsc
where cod_cuenta != 'N05416776X0001'
DUR_SESION_IP FEC_INICIO_IP SEG_CURR_DIA
------------- --------------- ------------
3049 20030704152112 -28079
1229 20030704151842 -30049
3340 20030704152434 -27586
97 20030704162453 -27210
1495 20030704144655 -31690
126 20030704153044 -30430
1980 20030704150000 -30420
218 20030704161416 -27726
1229 20030731235942 1211
82 20030731235838 -0
I dont understand how this is possible.... (I should see 0 instead of -0) May you give some hints?
November 03, 2003 - 7:23 am UTC
1 select
2 dur_sesion_ip,
3 FEC_INICIO_IP,
4 dur_sesion_ip -
5 (trunc(to_date(FEC_INICIO_IP,'YYYYMMDDHH24MISS') + 1) -
6 to_date(FEC_INICIO_IP,'YYYYMMDDHH24MISS')) * (24*3600) seg_curr_dia
7 from
8 (
9 select 82 dur_sesion_ip, '20030731235838' fec_inicio_ip
10 from dual
11* )
ops$tkyte@ORA920> /
DUR_SESION_IP FEC_INICIO_IP SEG_CURR_DIA
------------- -------------- ------------
82 20030731235838 -2.240E-36
its a very small number. I think you used numformat to control the display:
ops$tkyte@ORA920> set numformat 9999999999999
ops$tkyte@ORA920> /
DUR_SESION_IP FEC_INICIO_IP SEG_CURR_DIA
-------------- -------------- --------------
82 20030731235838 -0
so, it is "normal"
I dont understand why this data behave this way
A reader, November 03, 2003 - 8:06 am UTC
Hi
I dont understand why I get very small number (you are right, I formatted the column to get -0)
select
dur_sesion_ip,
(trunc(to_date(FEC_INICIO_IP,'YYYYMMDDHH24MISS') + 1) - to_date(FEC_INICIO_IP,'YYYYMMDDHH24MISS')) * (24*3600) x,
dur_sesion_ip -
(trunc(to_date(FEC_INICIO_IP,'YYYYMMDDHH24MISS') + 1) - to_date(FEC_INICIO_IP,'YYYYMMDDHH24MISS')) * (24*3600) z
from bvg_idrs_lsc
where cod_cuenta = 'N09381005H0001'
DUR_SESION_IP X Z
------------- ---------- ----------
218 27944 -27726
82 82 -2.240E-36
89970 82 89888
82 82 -2.240E-36
82 - 82 is 0 :-0
November 03, 2003 - 2:11 pm UTC
it isn't 82.
ops$tkyte@ORA920PC> select dur_sesion_ip,
2 (trunc(to_date(FEC_INICIO_IP,'YYYYMMDDHH24MISS') + 1) -
3 to_date(FEC_INICIO_IP,'YYYYMMDDHH24MISS')) * (24*3600) x,
4 dur_sesion_ip -
5 (trunc(to_date(FEC_INICIO_IP,'YYYYMMDDHH24MISS') + 1) -
6 to_date(FEC_INICIO_IP,'YYYYMMDDHH24MISS')) * (24*3600) z
7 from (select 82 dur_sesion_ip, '20030731235838' fec_inicio_ip from dual )
8 /
DUR_SESION_IP X Z
------------- ---------- ----------
82 82 -2.240E-36
ops$tkyte@ORA920PC> set numformat 999.9999999999999999999999999999999999999999
ops$tkyte@ORA920PC> /
DUR_SESION_IP X
--------------------------------------------- ---------------------------------------------
Z
---------------------------------------------
82.0000000000000000000000000000000000000000 82.0000000000000000000000000000000000022400
-.0000000000000000000000000000000000022400
Bug in LTRIM??
Mani, August 18, 2009 - 12:54 pm UTC
Am in 10GR2.
select * from v$version;
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
"CORE 10.2.0.4.0 Production"
TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio
NLSRTL Version 10.2.0.4.0 - Production
when i run
SELECT LTRIM( 'VALID Duplicate Record. ','VALID ') x FROM DUAL;
x
----------------
Duplicate Record.
which is ok, but when i change it to
SELECT LTRIM( 'VALIDDuplicate Record. ','VALID') x FROM DUAL;
x
-----------------
uplicate Record.
which i believe is a bug. but strangely when i slightly change the query to
SELECT LTRIM( 'VALIDduplicate Record. ','VALID') x FROM DUAL;
x
---------------
duplicate Record.
it changes back, it appears when last character of trim is repeated it just skips it.
SELECT LTRIM( 'VALID Duplicate Record. ','VALID ') x FROM DUAL;
x
------------------
uplicate Record.
pretty strange. not sure its already reported.
Thanks
Mani
August 24, 2009 - 4:43 pm UTC
my first thought would have been "read the documentation, maybe what I think to be true is not what is"
http://docs.oracle.com/docs/cd/B19306_01/server.102/b14200/functions083.htm#SQLRF00664 The keyword 'set' popped right out.
ops$tkyte%ORA11GR1> select ltrim( 'ABBA Rocks', 'ABC ' ) from dual;
LTRIM
-----
Rocks
<quote>
LTRIM removes from the left end of char all of the characters contained in
set.
</quote>
the second string is not a 'string', it is a set of characters to be trimmed.
Not a bug, not even close.
Always something new to learn..
Learner, August 30, 2009 - 12:53 pm UTC
Excellent..been using LTRIM for ages..didnt realize the subtle difference between character match vs character in set.
Thanks for the question (Mani) and of course, to Tom for his usual simple & crisp answer.. took a while for me to understand it..after running it multiple times. :)
ltrim(to_char(..).. doesn't work?
Shuchi, November 12, 2009 - 11:18 am UTC
Hello Tom, A simple query that has me baffled. Why isn't LTRIM working in the 3rd query? I expected the output to be 12.3, not 0012.3.
SQL> select ltrim('0012.3', '0') from dual;
LTRI
----
12.3
SQL> select to_char(12.3, '0000.0') from dual;
TO_CHAR
-------
0012.3
SQL> select ltrim(to_char(12.3, '0000.0'),'0') from dual;
LTRIM(T
-------
0012.3
I know LTRIM shouldn't be used here, formatting with 9s in place of 0s is the right way. Still asking to understand why LTRIM won't do it at all...
November 15, 2009 - 2:14 pm UTC
see the leading space for the sign?
ops$tkyte%ORA10GR2> select ltrim(to_char(12.3, '0000.0'),'0') from dual;
LTRIM(T
-------
0012.3
ops$tkyte%ORA10GR2> select ltrim(to_char(12.3, 'fm0000.0'),'0') from dual;
LTRIM(T
-------
12.3
fm (format modifier) removes unnecessary white space - like the leading space.
ops$tkyte%ORA10GR2> select to_char( sysdate, 'Day Month YYYY' ) from dual;
TO_CHAR(SYSDATE,'DAYMONT
------------------------
Sunday November 2009
ops$tkyte%ORA10GR2> select to_char( sysdate, 'fmDay Month YYYY' ) from dual;
TO_CHAR(SYSDATE,'FMDAYMO
------------------------
Sunday November 2009
Difference in format
A reader, May 01, 2010 - 2:22 am UTC
Hi Tom,
What is the difference between
SELECT TO_CHAR(1,'fm00000') FROM DUAL;
and
SELECT TO_CHAR(1,'fm00009') FROM DUAL;
I need 4 leading 0's for an integer input. Under what situations, I should 9 in format?
Thanks
May 06, 2010 - 10:36 am UTC
ops$tkyte%ORA11GR2> select to_char( x, 'fm09999'), to_char(x, 'fm00000' ) from (select 0 x from dual);
TO_CHA TO_CHA
------ ------
00000 00000
either would work - as would 09999, the 0 says to "use leading zeroes from here on in"