Oracle RPAD() padding with an empty string

Question

I am trying to create a dump file from within SQL*Plus. The requirement is to create null '' for padding but when I use NULL even the data value is getting nullified see below.

SQL> select RPAD(1234,10,' ') from dual ;

RPAD(1234,
----------
1234

SQL> select RPAD(1234,10,'') from dual;

R
-

I have seen other scripts where they seem to be using null('') for padding

Please help thanks

What exactly are you trying to do? What sense does it make to pad a value with "nothing"? — user330315
– user330315, Commented Sep 11, 2013 at 21:55
NULL and empty string are not the same. It makes no sense to pad with either of them. Usually single space is used for padding. Are you possibly mistaking '' for ' '? — PM 77-1
– PM 77-1, Commented Sep 11, 2013 at 22:22
@PM77-1 in oracle null and empty string ARE the same :) try select * from dual where '' is null; — Kirill Leontev
– Kirill Leontev, Commented Sep 12, 2013 at 4:23

Jeffrey Kemp · Accepted Answer · 2013-09-12 04:10:21Z

4

RPAD accepts a character or string as its 3rd parameter which is used to "pad" the initial string to a particular length.

RPAD can be used to return a string which is "guaranteed" to be n characters long (as per the 2nd parameter).

Since NULL does not represent any particular character or string and has zero length, it cannot be used for padding - RPAD apparently returns NULL in this instance, which makes sense as the only other option would be for RPAD to raise an exception.

answered Sep 12, 2013 at 4:10

Jeffrey Kemp

60.5k15 gold badges111 silver badges161 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

javaPlease42 Over a year ago

Yep. “RPAD(expr1 , n [, expr2 ])… expr1 cannot be null.” Reference

Jeffrey Kemp Over a year ago

@javaPlease42, yes - but the OP was talking about expr2, which can be null (although not very useful if used that way).

adona9 · Accepted Answer · 2013-09-12 00:50:29Z

-1

This code:

RPAD(1234,10,'')

concatenates 1234 to '', which in Oracle is equivalent to NULL, therefore it results in NULL (anything concatenated to NULL yields NULL)

There is no NULL('') in Oracle.

Hope that helps.

answered Sep 12, 2013 at 0:50

adona9

3692 silver badges4 bronze badges

Collectives™ on Stack Overflow

Oracle RPAD() padding with an empty string

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related