REPEAT Function
Repeats a string a specified number of times. The string can be specified as a String literal, a function returning a String, or a column reference.
Since the
REPEAT
function matches based on fixed numeric values, changes to the length or structure of a data field can cause your recipe to fail to properly execute.The
REPEAT
function requires an integer value for the number of characters to match.
Wrangle vs. SQL: This function is part of Wrangle, a proprietary data transformation language. Wrangle is not SQL. For more information, see Wrangle Language.
Basic Usage
String literal example:
repeat('ha',3)
Output: Returns the string: hahaha
.
Column reference example:
repeat(MyString,4)
Output: Returns the values of the MyString
column value written four times in a row.
Syntax and Arguments
repeat(column_string,rpt_count)
Argument | Required? | Data Type | Description |
---|---|---|---|
column_string | Y | string | Name of the column or string literal to be applied to the function |
rpt_count | N | integer (positive) | Count of times to repeat the string |
For more information on syntax standards, see Language Documentation Syntax Notes.
column_string
Name of the column or String literal to be repeated.
Missing string or column values generate missing string results.
String constants must be quoted (
'Hello, World'
).Multiple columns and wildcards are not supported.
Usage Notes:
Required? | Data Type | Example Value |
---|---|---|
Yes | String literal, function, or column reference | myColumn |
rpt_count
Count of times to repeat the string.
If the value is not specified, the default is
1
.Value must a non-negative integer.
References to columns of integer data type are not supported.
Usage Notes:
Required? | Data Type | Example Value |
---|---|---|
No | Integer (non-negative) | 5 |
Examples
Astuce
For additional examples, see Common Tasks.
Example - REPEAT string function
Source:
myStr | repeat_count |
---|---|
ha | 0 |
ha | 1 |
ha | 1.5 |
ha | 2 |
ha | -2 |
Transformation:
Transformation Name | |
---|---|
Parameter: Formula type | Single row formula |
Parameter: Formula | repeat(myStr,repeat_count) |
Parameter: New column name | 'repeat_string' |
Results:
myStr | repeat_count | repeat_string |
---|---|---|
ha | 0 | |
ha | 1 | ha |
ha | 1.5 | |
ha | 2 | haha |
ha | -2 |
Example - Padding values
In the following example, the imported prodId
values are supposed to be eight characters in length. Somewhere during the process, however, leading 0
characters were truncated. The steps below allow you to re-insert the leading characters.
Source:
prodName | prodId |
---|---|
w01 | 1 |
w02 | 10000001 |
w03 | 345 |
w04 | 10402 |
Transformation:
First, we need to identify how many zeroes need to be inserted for each prodId:
Transformation Name | |
---|---|
Parameter: Formula type | Single row formula |
Parameter: Formula | 8 - len(prodId) |
Parameter: New column name | 'len_prodId' |
Use the REPEAT
function to generate a pad string based on the above values:
Transformation Name | |
---|---|
Parameter: Formula type | Single row formula |
Parameter: Formula | repeat('0', len_prodId) |
Parameter: New column name | 'padString' |
Merge the pad string and the original prodId column:
Transformation Name | |
---|---|
Parameter: Columns | padString,prodId |
Parameter: Separator | '' |
Parameter: New column name | 'column2' |
Results:
When you delete the intermediate columns and rename column2
to prodId
, you have the following table:
prodName | prodId |
---|---|
w01 | 00000001 |
w02 | 10000001 |
w03 | 00000345 |
w04 | 00010402 |