Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Published by Scroll Versions from space DEV and version r0761

D toc

Excerpt

Removes leading and trailing quotes or double-quotes from a string. Quote marks in the middle of the string are not removed.

  • This function applies to both single quotes (') and double quotes (").
    • This function is not limited to removing the outer set of quotes only. If there are multiple quotes at the beginning or the end of the string (""), all sets of quotes are removed.
  • The TRIMQUOTES function does not remove whitespace at the beginning and end. 

    Tip

    Tip: You may need to nest this function and the TRIM function to clean up your strings. An example is provided below.

D s lang vs sql

D s
snippetBasic

Column reference example:

D lang syntax
RawWrangletrue
Typeref
showNotetrue
WrangleTextderive 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
RawWrangletrue
Typeref
showNotetrue
WrangleTextderive 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
RawWrangletrue
Typeref
showNotetrue
WrangleTextderive 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
RawWrangletrue
Typeref
showNotetrue
WrangleTextderive 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 s
snippetSyntax

D lang syntax
RawWrangletrue
Typesyntax
showNotetrue
WrangleTextderive type:single value:trimquotes(column_string)

trimquotes(column_string)


ArgumentRequired?Data TypeDescription
column_stringYstringName of the column or string literal to be applied to the function

D s lang notes

column_string

Name of the column or string constant whose beginning and end quote marks are 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.

D s
snippetusage

Required?Data TypeExample Value
YesString literal or column referencemyColumn

D s
snippetExamples

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:

StringDescription
My String"Base string: ""My String"""
My String extra"Base string + "" extra"""
 My StringA space in front of base string
My String A space after base string
MyStringNo space between the two words of base string
My  StringTwo 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.

  • The wildcard (*) for columns indicates that this formula should be applied across all columns in the dataset. 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
RawWrangletrue
Typestep
WrangleTextset col: * value: trimquotes(trim($col))
p01NameColumns
p01Value*
p02NameFormula
p02Valuetrimquotes(trim($col))
SearchTermEdit column with formula

Results:

StringDescription
My StringBase string: ""My String
My String extraBase string + "" extra
My StringA space in front of base string
My StringA space after base string
MyStringNo space between the two words of base string
My  StringTwo spaces between the two words of base string
My StringBase string + a tab character
My StringBase string + a return character
My StringBase string + a newline character


D s also
labelstring