Skip to main content

TRIM Functions

The TRIM functions remove whitespace or specified characters from strings.

  • TRIM removes characters from both ends.
  • LTRIM removes characters from the left (start).
  • RTRIM removes characters from the right (end).

Syntax and Parameters​

TRIM(string , characters)
LTRIM(string , characters)
RTRIM(string , characters)
  • String: The input string to trim.
  • Characters: Optional characters to remove. If omitted, removes whitespace.

Basic Usage​

Using ' data science ' and '/path/to/file/' as examples:

-- Trimming whitespace
TRIM('  data science  ') -- Returns "data science"
LTRIM('  data science  ') -- Returns "data science  "
RTRIM('  data science  ') -- Returns "  data science"

-- Trimming specific characters
TRIM('/path/to/file/', '/') -- Returns "path/to/file"
LTRIM('/path/to/file/', '/') -- Returns "path/to/file/"
RTRIM('/path/to/file/', '/') -- Returns "/path/to/file"

Common Use Cases​

Cleaning input data:

Remove unwanted padding in imported data.

-- Basic whitespace cleanup
TRIM({{SRC}}) -- "  customer name  " → "customer name"

-- Removing specific characters
LTRIM({{SRC}}, '0') -- "000123" → "123"
RTRIM({{SRC}}, '.') -- "price.00" → "price"

-- Combined trimming for both ends
TRIM({{SRC}}, '/') -- "/path/to/file/" → "path/to/file"

Standardizing identifiers:

Clean up identifier fields that may have padding.

-- Remove leading zeros
LTRIM({{SRC}}, '0') -- "000366631" → "366631"

-- Remove trailing spaces from fixed-width fields
RTRIM({{SRC}}) -- "CUSTOMER_ID    " → "CUSTOMER_ID"

-- Remove both leading zeros and trailing spaces
TRIM(LTRIM({{SRC}}, '0')) -- "00012345    " → "12345"

Using TRIM Functions in Coalesce​

When using TRIM functions in transforms and bulk editing, you can use Helper Tokens:

TRIM({{SRC}})

The {{SRC}} token automatically gets replaced with the fully qualified source Node and column name. For example:

  • If source column is "CUSTOMER"."C_NAME" it becomes TRIM("CUSTOMER"."C_NAME")
  • If source column is "ORDERS"."ORDER_ID" it becomes TRIM("ORDERS"."ORDER_ID")