| Excerpt |
|---|
Removes leading and trailing quotes or double-quotes from a string. Quote marks in the middle of the string are not removed. |
Column reference example:
| D lang syntax |
|---|
| RawWrangle | true |
|---|
| Type | ref |
|---|
| showNote | true |
|---|
| WrangleText | derive type:single value:trimquotes(MyName) |
|---|
|
trimquotes(MyName) |
Output: Returns the values of the MyName column value with any quotes removed from the beginning and the end.
String literal examples:
| Info |
|---|
NOTE: For string literal values that contain quotes to remove, you can bracket them in the quote mark of a different type. Some examples are below. |
| D lang syntax |
|---|
| RawWrangle | true |
|---|
| Type | ref |
|---|
| showNote | true |
|---|
| WrangleText | derive type:single value:trimquotes(' Hello, World ') |
|---|
|
trimquotes('"Hello, World"') |
Output: Input string is "Hello, World". Output of the function is the string: Hello, World.
| D lang syntax |
|---|
| RawWrangle | true |
|---|
| Type | ref |
|---|
| showNote | true |
|---|
| WrangleText | derive type:single value:trimquotes(' Hello, World ') |
|---|
|
trimquotes('""Hello,\" World""') |
Output: Input string is ""Hello,\" World"". Output of the function is the string: Hello," World.
Following input contains a single whitespace at the beginning and end of the string, which is the same as the previous string. To clean, you can first remove the whitespace with the TRIM function.
| D lang syntax |
|---|
| RawWrangle | true |
|---|
| Type | ref |
|---|
| showNote | true |
|---|
| WrangleText | derive type:single value:trimquotes(' Hello, World ') |
|---|
|
trimquotes(trim((' ""Hello,\" World"" '))) |
Output: Input string is ""Hello,\" World"" . Output of the function is the string: Hello," World. See TRIM Function.
| D lang syntax |
|---|
| RawWrangle | true |
|---|
| Type | syntax |
|---|
| showNote | true |
|---|
| WrangleText | derive type:single value:trimquotes(column_string) |
|---|
|
trimquotes(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 whose beginning and end quote marks are to be trimmed.
| Required? | Data Type | Example Value |
|---|
| Yes | String literal or column reference | myColumn |
Example - String whitespace and quotes
The following example data contains a mixture of quotes and spaces strings. You can use the transformation listed below to clean up leading and trailing quotes and strings in a single transformation.
Source:
| String | Description |
|---|
| My String | "Base string: ""My String""" |
| My String extra | "Base string + "" extra""" |
| My String | A space in front of base string |
| My String | A space after base string |
| MyString | No space between the two words of base string |
| My String | Two spaces between the two words of base string |
| "My String " | Base string + a tab character |
"My String
" | Base string + a return character |
"My String
" | Base string + a newline character |
Transformation:
You can use the following transformation which nests the TRIM and the TRIMQUOTES functions to clean up all of the columns in your dataset.
- To apply across all columns in the dataset:
- The wildcard (
*) for columns indicates that this formula should be applied across all columns in the dataset. - You can also select
All from the Columns drop-down in the Transform Builder. - Since all columns are String type, the results should be consistent.
- The
$col reference can be used to refer to the current column that is being evaluated. For more information, see Source Metadata References.
| D trans |
|---|
| RawWrangle | true |
|---|
| Type | step |
|---|
| WrangleText | set col: * value: trimquotes(trim($col)) |
|---|
| p01Name | Columns |
|---|
| p01Value | All |
|---|
| p02Name | Formula |
|---|
| p02Value | trimquotes(trim($col)) |
|---|
| SearchTerm | Edit column with formula |
|---|
|
Results:
| String | Description |
|---|
| My String | Base string: ""My String |
| My String extra | Base string + "" extra |
| My String | A space in front of base string |
| My String | A space after base string |
| MyString | No space between the two words of base string |
| My String | Two spaces between the two words of base string |
| My String | Base string + a tab character |
| My String | Base string + a return character |
| My String | Base string + a newline character |
