CODEWITHSUNDEEP
sqloracle-database
Prema kumari
Prema kumari

Reputation: 171

TO_CHAR function logic <<Number format>>

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

Answers (3)

CompEng
CompEng

Reputation: 7416

if you try like this

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

David דודו Markovitz
David דודו Markovitz

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

Rene
Rene

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

Related Questions