Author Topic: PL/SQL - Using Rpad function return additional whitespace  (Read 4794 times)

jfpueh

  • Newbie
  • *
  • Posts: 2
    • View Profile
PL/SQL - Using Rpad function return additional whitespace
« on: December 15, 2009, 03:34:10 AM »
When I'm using the rpad function with simple select statement in Oracle SQL*Plus such as :-
 
select rpad(full_name,1,35) x from vendors where rownum < 5
/
The results return as
X
----------------------------------------------------------------------------------------------------
W
B
Y
G
which I would like to know what setting which will cause the additional whitespace ?


Mike

  • Administrator
  • Hero Member
  • *****
  • Posts: 2052
    • View Profile
Re: PL/SQL - Using Rpad function return additional whitespace
« Reply #1 on: December 15, 2009, 06:11:25 AM »
Check out this page (scroll down to "RPAD") and see if it doesn't answer your question:

http://www.psoug.org/reference/string_func.html
                                 
RPAD
Right Pad
 
Overload 1
RPAD(str1 VARCHAR2 CHARACTER SET ANY_CS, len PLS_INTEGER,
  pad VARCHAR2 CHARACTER SET STR1%CHARSET)
  RETURN VARCHAR2 CHARACTER SET STR1%CHARSET;


SELECT RPAD('John Smith', 25, 'x') FROM dual;
Overload 2RPAD(str1 VARCHAR2 CHARACTER SET ANY_CS, len PLS_INTEGER)
  RETURN VARCHAR2 CHARACTER SET STR1%CHARSET;

SELECT RPAD('John Smith', 25) ||'<-' FROM dual;

jfpueh

  • Newbie
  • *
  • Posts: 2
    • View Profile
Re: PL/SQL - Using Rpad function return additional whitespace
« Reply #2 on: December 15, 2009, 06:48:23 PM »
There is no issue with the rpad function, is only the result that I have get have return additional space even the field size is not that long. I've try execute the command provided but it have return the results as shown :-
 
SQL> SELECT RPAD('John Smith', 25, 'x') FROM dual;

RPAD('JOHNSMITH',25,'X')
----------------------------------------------------------------------------------------------------
John Smithxxxxxxxxxxxxxxx

 
SQL> SELECT RPAD('John Smith', 25) ||'<-' x FROM dual;
 
RPAD('JOHNSMITH',25)||'<-'
----------------------------------------------------------------------------------------------------
John Smith         <-
 
I know the correct return result should be
 
RPAD('JOHNSMITH',25)||'<-'
---------------------------
John Smith         <-
 
Is it due to  any system setting which cause the above issue, as it have impact to all my scripts.  Thanks.