REPEAT scalar function

The REPEAT function returns a character string that is composed of the first argument repeated the number of times that are specified by the second argument.

The schema is SYSIBM. The SYSFUN version of the REPEAT function continues to be available.

expression1

An expression that specifies the string to be repeated. The expression must return a built-in character string, numeric value, Boolean value, or datetime value. If the value is not a character string, it is implicitly cast to VARCHAR before the function is evaluated.

expression2

An expression that is a positive integer value or zero that specifies the number of times to repeat the string. The expression must return a built-in character string, graphic string, or numeric value. If the value is not an integer, it is implicitly cast to INTEGER before the function is evaluated.

Result

The result of the function is one of the following data types:

VARBINARY if expression1 is a BINARY or VARBINARY string
VARCHAR if expression1 is a CHAR or VARCHAR string
VARGRAPHIC if expression1 is GRAPHIC or VARGRAPHIC string
CLOB if expression1 is CLOB
BLOB if expression1 is BLOB
DBCLOB if expression1 is a DBCLOB

If expression2 is a constant, the length attribute of the result is minimum of the length attribute of expression1 times expression2 and the maximum length of the result data type. Otherwise, the length attribute depends on the data type of the result:

4000 for VARBINARY and VARCHAR
2000 for VARGRAPHIC
1 MB for CLOB, DBCLOB, and BLOB

The actual length of the result is the actual length of expression1 times expression2. If the actual length of the result string exceeds the length attribute for the return type, an error is returned (SQLSTATE 54006).

If the result data type is a character string or graphic string, the string units of the result are the string units of expression1.

If any argument can be null, the result can be null; if any argument is null, the result is the null value.

Examples

Repeat 'abc' two times to create 'abcabc'.

   SELECT REPEAT('abc',2)
     FROM SYSIBM.SYSDUMMY1

List the phrase 'REPEAT THIS' five times. Use the CHAR function to limit the output to 60 bytes.

   SELECT CHAR(REPEAT('REPEAT THIS',5), 60)
     FROM SYSIBM.SYSDUMMY1

This example outputs the following string:

   'REPEAT THISREPEAT THISREPEAT THISREPEAT THISREPEAT THIS     '

For the following query, the LENGTH function returns a value of 0 because the result of repeating a string zero times is an empty string, which is a zero-length string.
```
   SELECT LENGTH(REPEAT('REPEAT THIS',0))
     FROM SYSIBM.SYSDUMMY1
```
For the following query, the LENGTH function returns a value of 0. A value of 0 because the result of repeating an empty string any number of times is an empty string, which is a zero-length string.
```
   SELECT LENGTH(REPEAT('', 5))
     FROM SYSIBM.SYSDUMMY1
```