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 next

D toc

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.

D s
snippetBasic

Column reference example:

D lang syntax
RawWrangletrue
Typeref
showNotetrue
WrangleTextderive 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
RawWrangletrue
Typeref
showNotetrue
WrangleTextderive type:single value:trim(' Hello, World ')

trim(' Hello, World ')


Output:
Returns the string:Hello, World.

D s
snippetSyntax

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

trim(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 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 - 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/.
ValueDefinitionASCII value
(space)spacebarChar(32)
(tab)tab characterChar(9)
(cr)carriage returnChar(13)
(nl)newlineChar(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
RawWrangletrue
p03Value''
Typestep
WrangleTextreplace col: mystring on: `"` with: '' global: true
p01NameColumn
p01Valuemystring
p02NameFind
p02Value`"`
p03NameReplace with
p04Valuetrue
p04NameMatch all occurrences
SearchTermReplace text or pattern

Now, you can apply the trim function:

D trans
RawWrangletrue
p03Value'trim_mystring'
Typestep
WrangleTextderive type:single value: trim(mystring) as: 'trim_mystring'
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02Valuetrim(mystring)
p03NameNew column name
SearchTermNew formula

Results:

In the generated trim_mystring column, you can see the cleaned strings:

mystringtrim_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.

D s also
labelstring

Example - String cleanup functions together

Include Page
EXAMPLE - String Cleanup Functions
EXAMPLE - String Cleanup Functions