REPEAT function equivalent in Oracle

Question

I would like to know how to achieve the same functionality as REPEAT() in SQL*Plus. For example consider this problem: display the character '*' as many times as the value specified by an integer attribute specified for each entry in a given table.

Answer 1

Nitpicking: SQL*Plus doesn't have any feature for that. The database server (Oracle) provides the ability to execute SQL and has such a function:

You are looking for rpad()

select rpad('*', 10, '*')
from dual;

will output

**********

More details can be found in the manual: https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions159.htm#SQLRF06103

Answer 2

For single characters, the accepted answer works fine.

However, If you have multiple characters in a given string, you need to use RPAD along with length function like this.

WITH t (str) AS
  ( 
   SELECT 'a'
   FROM DUAL
   UNION ALL SELECT 'abc'
   FROM DUAL
   UNION ALL SELECT '123'
   FROM DUAL
   UNION ALL SELECT '#+-'
   FROM DUAL
  )
SELECT RPAD(str, 5*LENGTH(str), str) repeated_5_times
FROM t;

Output:

REPEATED_5_TIMES
---------------
aaaaa
abcabcabcabcabc
123123123123123
#+-#+-#+-#+-#+-