REPEAT

The REPEAT function returns a string composed of expression repeated integer times.

REPEAT(expression,integer)
expression
An expression that specifies the string to be repeated. The string must be a built-in numeric or string expression. A numeric argument is cast to a character string before evaluating the function. For more information on converting numeric to a character string, see VARCHAR.
integer
An expression that returns a built-in BIGINT, INTEGER, or SMALLINT data type whose value is a positive integer or zero. The integer specifies the number of times to repeat the string.

The data type of the result of the function depends on the data type of the first argument:

Data type of expression Data type of the Result
CHAR or VARCHAR or any numeric type VARCHAR
CLOB CLOB
GRAPHIC or VARGRAPHIC VARGRAPHIC
DBCLOB DBCLOB
BINARY or VARBINARY VARBINARY
BLOB BLOB

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

  • 1,048,576 for BLOB, CLOB, or DBCLOB
  • 4000 for VARCHAR or VARBINARY
  • 2000 for VARGRAPHIC

The actual length of the result is the actual length of expression times integer. If the actual length of the result string exceeds the maximum for the return type, an error is returned.

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

The CCSID of the result is the CCSID of expression.1

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 results in '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 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
1 If the value of expression is mixed data that is not a properly formed mixed data string, the result will not be a properly formed mixed data string.