Reputation: 171
I am new to oracle and found something hard to understand. Even though i understand the functionality of TO_CHAR , i am new to concept of number format model. Please help me understand the below logic.
select TRIM(substr(TO_CHAR (160, '000'),1,3)) from dual;
Output -> 16
select TRIM(substr(TO_CHAR (160),1,3)) from dual;
Output -> 160
For the 1st query why has the oracle returned the value 16 rather than 160?
Upvotes: 2
Views: 730
Reputation: 7416
select substr(TRIM(TO_CHAR (160, '000')),1,3) from dual;
Output -> 160
instead of this:
select TRIM(substr(TO_CHAR (160, '000'),1,3)) from dual;
Upvotes: 0
Reputation: 44991
Start the format string with FM
, e.g. 'FM000'
.
Without the FM
there is a preceding space saved for a minus sign.
select '|' || to_char(160,'000') || '|' as no_FM
,'|' || to_char(160,'FM000') || '|' as with_FM
from dual
+--------+---------+
| NO_FM | WITH_FM |
+--------+---------+
| | 160| | |160| |
+--------+---------+
Upvotes: 3
Reputation: 10551
Interesting. Tried this:
select to_char(160, '000')
,substr(to_char(160, '000'), 1, 3)
,substr('160', 1, 3)
,length(to_char(160, '000'))
from dual;
Which gave me 160, 16 , 160, 4
The substring gives you the first 3 characters of the string. The fist character of to_char(160,'000') is a space. A place is reserved for a minus sign. So the value of substr(to_char(160, '000'), 1, 3) is not 16 but space16.
Upvotes: 2