Is there any function to add space in PL / SQL

Question

In T-SQL, the SPACE () function is used to add spaces to a string. E.g.

@s = 'He' + space(5) + 'llo' 

Exit

 He llo 

So, is there any function in PL / SQL equivalent to SPACE ()?

Thanks.

Answer 1

You can use the functions RPAD or LPAD

 select 'He' || rpad(' ',5,' ') || 'llo' from dual; / 

or in PL / SQL it will be:

 declare x varchar2(20); begin x:= 'He' || rpad(' ',5,' ') || 'llo'; end; / 

Answer 2

Jeffrey using rpad(' ',n,' ') gives n+1 spaces

 select RPAD('A',3,'-')||RPAD(' ',4,' ')||RPAD('B',5,'-') from dual 

Exit

 A-- B---- 

After A-- and before B you will find 5 spaces instead of 4.

Answer 3

In Oracle, you can emulate the space() function with:

 rpad(' ',n,' ') 

Where n less than the total length of the string of spaces you want.

eg. for a line of 40 spaces:

 select rpad(' ',39,' ') as "40 spaces" from dual 

dual is a dummy table that you can use to test calculations and string manipulations without extracting real life from the table.