REPEAT
The REPEAT function returns a string composed of expression repeated integer times.
- 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.
This example results in 'REPEAT THISREPEAT THISREPEAT THISREPEAT THISREPEAT THIS '.SELECT CHAR( REPEAT('REPEAT THIS', 5), 60) FROM SYSIBM.SYSDUMMY1
- 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