CODEWITHSUNDEEP
sqloracle-database
vivek ashodha
vivek ashodha

Reputation: 127

Oracle RPAD() padding with an empty string

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

Upvotes: 1

Views: 19614

Answers (2)

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60262

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.

Upvotes: 4

adona9
adona9

Reputation: 369

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.

Upvotes: -1

Related Questions