EXAMPLE - String Cleanup Functions
This example demonstrates functions that can be used to clean up strings.
Functions:
Item | Description |
|---|---|
TRIM Function | Removes leading and trailing whitespace from a string. Spacing between words is not removed. |
REMOVEWHITESPACE Function | Removes all whitespace from a string, including leading and trailing whitespace and all whitespace within the string. |
REMOVESYMBOLS Function | Removes all characters from a string that are not letters, numbers, accented Latin characters, or whitespace. |
Source:
In the following (space) and (tab) indicate space keys and tabs, respectively. Carriage return and newline characters are also supported by whitespace functions.
Strings | source |
|---|---|
String01 | this source(space)(space) |
String02 | (tab)(tab)this source |
String03 | (tab)(tab)this source(space)(space) |
String04 | this source's? |
String05 | Why, you @#$%^&*()! |
String06 | this söurce |
String07 | (space)this söurce |
String08 | à mañana |
Transformation:
The following transformation steps generate new columns using each of the string cleanup functions:
Transformation Name |
|
|---|---|
Parameter: Formula type | Single row formula |
Parameter: Formula | TRIM(source) |
Parameter: New column name | 'trim_source' |
Transformation Name |
|
|---|---|
Parameter: Formula type | Single row formula |
Parameter: Formula | REMOVEWHITESPACE(source) |
Parameter: New column name | 'removewhitespace_source' |
Transformation Name |
|
|---|---|
Parameter: Formula type | Single row formula |
Parameter: Formula | REMOVESYMBOLS(source) |
Parameter: New column name | 'removesymbols_source' |
Results:
Strings | source | removesymbols_source | removewhitespace_source | trim_source |
|---|---|---|---|---|
String01 | this source(space)(space) | this source(space)(space) | thissource | this source |
String02 | (tab)(tab)this source | (tab)(tab)this source | thissource | this source |
String03 | (tab)(tab)this source(space)(space) | (tab)(tab)this source(space)(space) | thissource | this source |
String04 | this source's? | this sources | thissource's? | this source's? |
String05 | "Why, you @#$%^&*()!" | Why you | Why,you@#$%^&*()! | Why, you @#$%^&*()! |
String06 | this söurce | this söurce | thissöurce | this söurce |
String07 | (space)this söurce | (space)this söurce | thissöurce | this söurce |
String08 | à mañana | à mañana | àmañana | à mañana |