TRIM Function

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.

Basic Usage

Column reference example:

derive value:TRIM(MyName)

Output: The value of the MyName column value with whitespace removed from the beginning and the end.

String literal example:

derive value:TRIM(' Hello, World ')

Output: The string Hello, World is written to the new column.

Syntax

derive value:TRIM(column_string)

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

For more information on syntax standards, see Language Documentation Syntax 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.

Usage Notes:

Required?Data TypeExample Value
YesString literal or column referencemyColumn

Examples

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)"

Transform:

You might notice the quote marks around most of the imported values.

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:

replace col: mystring on: `"` with: '' global: true

Now, you can apply the TRIM function:

derive value: TRIM(mystring) as: 'trim_mystring'

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: If any bracketing double quotes are removed, then tab, carriage return, and newline values are trimmed by the TRIM function.

Example - String cleanup functions together

The following example demonstrates functions that can be used to clean up strings. These functions include the following:

Source:

In the following (space) and (tab) indicate space keys and tabs, respectively. Carriage return and newline characters are also supported by whitespace functions.

Stringssource
String01this source(space)(space)
String02(tab)(tab)this source
String03(tab)(tab)this source(space)(space)
String04this source's?
String05Why, you @#$%^&*()!
String06this söurce
String07(space)this söurce
String08à mañana

Transform:

The following transforms generate new columns using each of the string cleanup functions:

derive value: TRIM(source) as: 'trim_source'

derive value: REMOVEWHITESPACE(source) as: 'removewhitespace_source'

derive value: REMOVESYMBOLS(source) as: 'removesymbols_source'

Results:

Stringssourceremovesymbols_sourceremovewhitespace_sourcetrim_source
String01this source(space)(space) this source(space)(space) thissourcethis source
String02(tab)(tab)this source(tab)(tab)this sourcethissourcethis source
String03(tab)(tab)this source(space)(space)(tab)(tab)this source(space)(space)thissourcethis source
String04this source's?this sourcesthissource's?this source's?
String05Why, you @#$%^&*()!Why you Why,you@#$%^&*()!Why, you @#$%^&*()!
String06this söurcethis surcethissöurcethis söurce
String07(space)this söurce(space)this surcethissöurcethis söurce
String08à mañana maanaà ma ñanaà ma ñana

Was this page helpful? Let us know how we did:

Send feedback about...

Google Cloud Dataprep Documentation