CODEWITHSUNDEEP
sqloracle-databasenumberspaddingto-char
Manim
Manim

Reputation: 83

RPAD TO_CHAR gives unexpected output - Oracle

I got unexpected output using rpad, nvl and to_char combination for number datatype.

The below is my query and executed in Oracle (Oracle Database 11g Release 11.2.0.3.0):

SELECT RPAD(NVL(TO_CHAR(122.85,'000.000'),' '),6) FROM dual

The output: 122.8 (note: the exact output: 122.8)

My doubt, why the last character 5) has truncated? Still the length is six including the decimal point.

Please help to understand the reason.

Regards, Mani

Upvotes: 1

Views: 1699

Answers (4)

krokodilko
krokodilko

Reputation: 36107

It's not truncatet, it is there.
But it is a space and you cannot see it ;)

SELECT RPAD(NVL(TO_CHAR(122.85,'000.000'),' '),6) x,
       length( RPAD(NVL(TO_CHAR(122.85,'000.000'),' '),6)) l,
       dump( RPAD(NVL(TO_CHAR(122.85,'000.000'),' '),6) ) dmp
FROM dual;

X               L DMP 
------ ---------- --------------------------------
 122.8          6 Typ=1 Len=6: 32,49,50,50,46,56

http://www.techonthenet.com/oracle/functions/rpad.php

RPAD( string, padded_length [, pad_string] )

pad_string is optional, if you do not specify it, oracle assumes a space.

Upvotes: 1

wanana
wanana

Reputation: 391

The code

SELECT NVL(TO_CHAR(122.85,'000.000'),' ') from dual;

return ' 122.850'

note the space before the number.

Upvotes: 1

schurik
schurik

Reputation: 7928

TO_CHAR(122.85,'000.000') returns > 122.850< with the blank space ahead. The blank space is reserved for the sign. you need the FM modifier in the format string: TO_CHAR(122.85,'FM000.000')

SELECT RPAD(NVL(TO_CHAR(122.85,'FM000.000'),' '),6) FROM dual

Upvotes: 1

Rachcha
Rachcha

Reputation: 8816

Somehow this TO_CHAR has added a space to the left of your number. I suggest you use the following:

SELECT RPAD(LTRIM(NVL(TO_CHAR(122.85,'000.000'),'')), 6)
FROM dual

Upvotes: 0

Related Questions