| Excerpt |
|---|
Pads string values to be a specified minimum length by adding a designated character to the left or right end of the string. Returned value is of String type. |
If an input value is longer than the minimum length, no change is made to the string. If you need to fit the string to be a specific length, you can use the LEFT, RIGHT, or SUBSTRING functions.
| Tip |
|---|
Tip: You can apply the following strings after you have applied padding to ensure all values are of the same length. |
Column reference example:
| D lang syntax |
|---|
| RawWrangle | true |
|---|
| Type | ref |
|---|
| showNote | true |
|---|
| WrangleText | set col: Whse_Nbr value: pad(Whse_Nbr, 6, '0', left) |
|---|
|
pad(Whse_Nbr, 6, '0', left) |
Output: Returns a value of a minimum of six characters in length. For input values that are shorter, the character 0 is added to the left side of the string.
String literal example:
| D lang syntax |
|---|
| RawWrangle | true |
|---|
| Type | ref |
|---|
| showNote | true |
|---|
| WrangleText | derive type:single value:pad('My Name', 10, '!', right) |
|---|
|
pad('My Name', 10, '!', right) |
Output: Returns the string: My Name!!!!.
| D lang syntax |
|---|
| RawWrangle | true |
|---|
| Type | syntax |
|---|
| showNote | true |
|---|
| WrangleText | derive type:single value:pad(string_val,string_length,pad_string,pad_side) |
|---|
|
pad(string_val,string_length,pad_string,pad_side) |
| Argument | Required? | Data Type | Description |
|---|
| string_val | Y | string | Name of the column, function returning string values, or string literal to be applied to the function |
| string_length | Y | integer (positive) | Minimum number of characters in the output string. |
| pad_string | N | string | String, column reference, or function returning a string to apply to strings that are less than the minimum length. Default is whitespace. |
| pad_side | N | enum | left - any padding is applied to the left side of the string (default)right - any padding is applied to the right side of the string
|
string_val
Name of the column, function returning a string, or string constant to be padded.
- Missing string or column values generate missing string results.
- String constants must be quoted (
'Hello, World'). - Multiple columns and wildcards are not supported.
| Required? | Data Type | Example Value |
|---|
| Yes | String literal or column reference | myColumn |
string_length
Minimum length of the generated string. Value is padded to this length at a minimum.
| Info |
|---|
NOTE: For input string values that are longer than the minimum string length, no padding is applied. |
- Negative values have no effect on the input string.
- References to columns of integer data type are not supported.
| Required? | Data Type | Example Value |
|---|
| Yes | Integer (non-negative) | 5 |
pad_string
The string of one or more characters that are used to pad input strings. If no value is provided, the default pad string is a single whitespace character.
Input values can be a string literal, a function returning a string, or a column containing strings.
Multi-character pad string behaviors:
When the pad string contains multiple characters, the behaviors are different depending on the side on which the string is padded:
| Function | Output Value |
|---|
| D lang syntax |
|---|
| RawWrangle | true |
|---|
| Type | ref |
|---|
| showNote | true |
|---|
| WrangleText | pad('12', 4, 'abc' left) |
|---|
| pad('12', 4, 'abc', left) |
| |
| D lang syntax |
|---|
| RawWrangle | true |
|---|
| Type | ref |
|---|
| showNote | true |
|---|
| WrangleText | pad('12', 4, 'abc' right) |
|---|
| pad('12', 4, 'abc', right) |
| |
| D lang syntax |
|---|
| RawWrangle | true |
|---|
| Type | ref |
|---|
| showNote | true |
|---|
| WrangleText | pad('12', 6, 'abc' left) |
|---|
| pad('12', 6, 'abc', left) |
| |
| D lang syntax |
|---|
| RawWrangle | true |
|---|
| Type | ref |
|---|
| showNote | true |
|---|
| WrangleText | pad('12', 6, 'abc' right) |
|---|
| pad('12', 6, 'abc', right) |
| |
| Required? | Data Type | Example Value |
|---|
| Yes | String literal, function returning a string, or column reference | 'X' |
pad_side
An enumerated value used to determine the side of the string to which any padding is applied:
| Value | Description |
|---|
left | Any padding is applied to the left side. This is the default value if not specified. |
right | Any padding is applied to the right side. |
| Required? | Data Type | Example Value |
|---|
| No | One of the following: left or right | left |
Example - Numeric identifiers
In the following example, a table containing four-character product identifiers and product names has been imported into
. Unfortunately, these product identifiers are numeric in structure and are therefore interpreted by
as integer values during import. The leading zeroes are dropped for some of the values, while the latter rows in the table contain fully defined numeric values.
Source:
| prodId | prodName |
|---|
| 1 | Our First Product |
| 2 | Our Second Product |
| 3 | Our First Product v2 |
| 1001 | A New Product Line |
| 1002 | A New Product Line v2 |
Transformation:
The first step is to convert the product identifiers to string values:
| D trans |
|---|
| RawWrangle | true |
|---|
| Type | step |
|---|
| WrangleText | settype col: prodId type: 'String' |
|---|
| p01Name | Columns |
|---|
| p01Value | prodId |
|---|
| p02Name | New type |
|---|
| p02Value | 'String' |
|---|
| SearchTerm | Change column data type |
|---|
|
Then, you can apply the character 0 as padding to the left of these strings, so that all values are four characters in length at a minimum:
| D trans |
|---|
| RawWrangle | true |
|---|
| Type | step |
|---|
| WrangleText | set col:prodId value:pad(prodId,4,'0',left) |
|---|
| p01Name | Columns |
|---|
| p01Value | ProdId |
|---|
| p02Name | Formula |
|---|
| p02Value | pad(prodId,4,'0',left) |
|---|
| SearchTerm | Edit column with formula |
|---|
|
Results:
| prodId | prodName |
|---|
| 0001 | Our First Product |
| 0002 | Our Second Product |
| 0003 | Our First Product v2 |
| 1001 | A New Product Line |
| 1002 | A New Product Line v2 |
