The REPEAT function returns a string created by concatenating a character string value a specified number of times.


REPEAT ( stringToRepeat, numOfRepeats )


An expression that specifies the string to be repeated. The expression must represent a value that is of type CHAR, VARCHAR, or LONG VARCHAR.

If this value is null, the REPEAT function returns null.


A non-negative integer value that specifies the number of times to concatenate stringToRepeat to form the resulting value.

If this value is 0 and stringToRepeat is non-null, then the REPEAT function returns the empty string.


The data type of the result is the same as the type of the stringToRepeat argument. The width of the resuls is calculated as follows:

  • If numOfRepeats is a known constant at bind time, the width of the result is:

    (maxWidth of stringToRepeat) * numOfRepeats

  • If numOfRepeats is an expression whose value is either dynamic or unknown at bind time (e.g. a parameter), the width of the result is:

    The maximum possible width of the type of the stringToRepeat argument.


We create a simple table for the examples in this section:

splice> CREATE TABLE t1 (a1 CHAR(5), b1 VARCHAR(5), c1 INT);
0 rows inserted/updated/deleted

splice> INSERT INTO t1 VALUES ('aaa', 'aaa', 3), ('bbbb', 'bbbb', 4);
2 rows inserted/updated/deleted

Example 1: Repeat Count is a Constant

splice> select repeat(a1, 3) from t1;
aaa  aaa  aaa
bbbb bbbb bbbb

2 rows selected

Example 2: Repeat Count is Parameterized

prepare q1 as 'select repeat(a1, ?) from t1';
splice> execute q1 using 'values (3)';
aaa  aaa  aaa
bbbb bbbb bbbb

2 rows selected

Example 3: Both Arguments are Expressions

splice> select repeat('-' || a1 || b1 || '-', c1) from t1;
-aaa  aaa--aaa  aaa--aaa  aaa-
-bbbb bbbb--bbbb bbbb--bbbb bbbb--bbbb bbbb-

2 rows selected