Removes leading and trailing quotes or double-quotes from a string. Quote marks in the middle of the string are not removed. |
'
) and double quotes ("
).""
), all sets of quotes are removed.The TRIMQUOTES
function does not remove whitespace at the beginning and end.
Tip: You may need to nest this function and the |
Column reference example:
trimquotes(MyName) |
Output: Returns the values of the MyName
column value with any quotes removed from the beginning and the end.
String literal examples:
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. |
trimquotes('"Hello, World"') |
Output: Input string is "Hello, World"
. Output of the function is the string: 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.
trimquotes(trim((' ""Hello,\" World"" '))) |
Output: Input string is ""Hello,\" World""
. Output of the function is the string: Hello," World
. See TRIM Function.
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 |
Name of the column or string constant whose beginning and end quote marks are to be trimmed.
String constants must be quoted ('Hello, World'
).
Required? | Data Type | Example Value |
---|---|---|
Yes | String literal or column reference | myColumn |
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.
*
) for columns indicates that this formula should be applied across all columns in the dataset. All
from the Columns drop-down in the Transform Builder.$col
reference can be used to refer to the current column that is being evaluated. For more information, see Source Metadata References.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 |