| Excerpt |
|---|
Removes leading and trailing whitespace from a string. Spacing between words is not removed. |
- If a string begins or ends with spaces, tabs, or other non-visible characters, they are removed by this function.
- The
TRIM function does not remove whitespace between non-whitespace values, such as spaces between words. To remove that type of whitespace, use REMOVEWHITESPACE. See REMOVEWHITESPACE Function. - The
TRIM function can be used with the TRIMQUOTES function, which removes leading and trailing single- and double-quotes. For more information, see TRIMQUOTES Function.
Column reference example:
| D lang syntax |
|---|
| RawWrangle | true |
|---|
| Type | ref |
|---|
| showNote | true |
|---|
| WrangleText | derive type:single value:trim(MyName) |
|---|
|
trim(MyName) |
Output: Returns the values of the MyName column value with whitespace removed from the beginning and the end.
String literal example:
| D lang syntax |
|---|
| RawWrangle | true |
|---|
| Type | ref |
|---|
| showNote | true |
|---|
| WrangleText | derive type:single value:trim(' Hello, World ') |
|---|
|
trim(' Hello, World ') |
Output: Returns the string:Hello, World.
| D lang syntax |
|---|
| RawWrangle | true |
|---|
| Type | syntax |
|---|
| showNote | true |
|---|
| WrangleText | derive type:single value:trim(column_string) |
|---|
|
trim(column_string) |
| Argument | Required? | Data Type | Description |
|---|
| column_string | Y | string | Name of the column or string literal to be applied to the function |
column_string
Name of the column or string constant to be trimmed.
- 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 |
Example - Trimming leading and trailing whitespace
In this example, whitespace values are identified according to this table. The ASCII value column identifies that ASCII character value that represents the character.
- The ASCII character set is a standard method for representing keyboard and special characters on the computer. For more information on ASCII, see http://www.asciitable.com/.
| Value | Definition | ASCII value |
|---|
| (space) | spacebar | Char(32) |
| (tab) | tab character | Char(9) |
| (cr) | carriage return | Char(13) |
| (nl) | newline | Char(10) |
Source:
In the following example dataset, input values are represented in the mystring. The values in the table above are represented in the string values below.
| mystring |
|---|
| Here's my string. |
| (space)(space)Here's my string.(space)(space) |
| (tab)Here's my string.(tab) |
| (cr)Here's my string.(cr) |
| (nl)Here's my string.(nl) |
| (space)(space)(tab)Here's my string.(tab)(space)(space) |
| (space)(space)(tab)(cr)Here's my string.(cr)(tab)(space)(space) |
| (space)(space)(tab)(nl)(cr)Here's my string.(cr)(nl)(tab)(space)(space) |
Input:
When the above CSV data is imported into the Transformer page, it is represented as the following:
| mystring |
|---|
| Here's my string. |
| (space)(space)Here's my string.(space)(space) |
| "(tab)Here's my string.(tab)" |
| "(cr)Here's my string.(cr)" |
| "(nl)Here's my string.(nl)" |
| "(space)(space)(tab)Here's my string.(tab)(space)(space)" |
| "(space)(space)(tab)(cr)Here's my string.(cr)(tab)(space)(space)" |
| "(space)(space)(tab)(nl)(cr)Here's my string.(cr)(nl)(tab)(space)(space)" |
Transformation:
You might notice the quote marks around most of the imported values.
| Info |
|---|
NOTE: If an imported string value contains tab, carriage return, or newline values, it is bracketed by double quotes. |
The first step is to remove the quote marks. You can select one of the quote marks in the data grid and then select the appropriate Replace suggestion card. The transform should look like the following:
| D trans |
|---|
| RawWrangle | true |
|---|
| p03Value | '' |
|---|
| Type | step |
|---|
| WrangleText | replace col: mystring on: `"` with: '' global: true |
|---|
| p01Name | Column |
|---|
| p01Value | mystring |
|---|
| p02Name | Find |
|---|
| p02Value | `"` |
|---|
| p03Name | Replace with |
|---|
| p04Value | true |
|---|
| p04Name | Match all occurrences |
|---|
| SearchTerm | Replace text or pattern |
|---|
|
Now, you can apply the trim function:
| D trans |
|---|
| RawWrangle | true |
|---|
| p03Value | 'trim_mystring' |
|---|
| Type | step |
|---|
| WrangleText | derive type:single value: trim(mystring) as: 'trim_mystring' |
|---|
| p01Name | Formula type |
|---|
| p01Value | Single row formula |
|---|
| p02Name | Formula |
|---|
| p02Value | trim(mystring) |
|---|
| p03Name | New column name |
|---|
| SearchTerm | New formula |
|---|
|
Results:
In the generated trim_mystring column, you can see the cleaned strings:
| mystring | trim_mystring |
|---|
| Here's my string. | Here's my string. |
| (space)(space)Here's my string.(space)(space) | Here's my string. |
| "(tab)Here's my string.(tab)" | Here's my string. |
| "(cr)Here's my string.(cr)" | Here's my string. |
| "(nl)Here's my string.(nl)" | Here's my string. |
| "(space)(space)(tab)Here's my string.(tab)(space)(space)" | Here's my string. |
| "(space)(space)(tab)(cr)Here's my string.(cr)(tab)(space)(space)" | Here's my string. |
| "(space)(space)(tab)(nl)(cr)Here's my string.(cr)(nl)(tab)(space)(space)" | Here's my string. |
| Tip |
|---|
Tip: If any bracketing double quotes are removed, then tab, carriage return, and newline values are trimmed by the TRIM function. |
Example - String cleanup functions together
| Include Page |
|---|
| EXAMPLE - String Cleanup Functions |
|---|
| EXAMPLE - String Cleanup Functions |
|---|
|
