DWHPro Insights

Understanding the Teradata TRIM Function: A Guide to Removing Spaces and Characters from Strings

By Roland Wenzlofsky ·

Understanding the Teradata TRIM Function: A Guide to Removing Spaces and Characters from Strings

Introduction

Data quality is important for effective analysis and reporting. Extra spaces in data can cause issues, but Teradata’s TRIM function can help. We will discuss using the TRIM function to clean and process data effectively.

Understanding the Teradata TRIM Function

The TRIM function in Teradata is a built-in string function that allows you to remove leading, trailing, or both leading and trailing spaces from a given string. The function can also be used to remove other specified characters. The syntax for the TRIM function is as follows:

TRIM([LEADING | TRAILING | BOTH] [character] FROM string)

The optional LEADING, TRAILING or BOTH keyword specifies which spaces to remove, and the optional ‘character’ argument allows you to specify a different character to remove instead of spaces. If no keyword is provided, the TRIM function defaults to BOTH.

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Basic Usage of TRIM

To demonstrate the basic usage of the TRIM function, let’s look at a few examples:

  1. Removing both leading and trailing spaces: SELECT TRIM(‘ DWHPro ‘) AS trimmed_string; This query will return the value ‘DWHPro’ without the leading and trailing spaces.
  2. Removing only leading spaces: SELECT TRIM(LEADING FROM ‘ DWHPro ‘) AS trimmed_string; This query will return the value ‘DWHPro ‘ with trailing spaces still present.
  3. Removing only trailing spaces: SELECT TRIM(TRAILING FROM ‘ DWHPro ‘) AS trimmed_string; This query will return the value ‘ DWHPro’ with leading spaces still present.

Using TRIM with Columns and Tables

In practice, you’ll often use the TRIM function to clean up data stored in columns within your tables. For example, suppose you have a table called ’employees’ with a column named ‘first_name’ that contains leading and trailing spaces. To remove those spaces, you can use the following query:

SELECT TRIM(first_name) AS trimmed_first_name FROM employees;

This query will return the ‘first_name’ column values without leading or trailing spaces.

Removing Custom Characters with TRIM

In addition to spaces, the TRIM function can also be used to remove other specified characters. To do this, provide the character you want to remove as an argument. For example, to remove leading and trailing ‘x’ from a string, you can use the following query:

SELECT TRIM(BOTH 'x' FROM 'xDWHProx') AS trimmed_string;

This exact input returns DWHPro. If the input is instead ‘x DWHProx ‘, the result is ‘ DWHProx ‘ (shown with boundaries: [ DWHProx ]): TRIM removes only the specified character at the edges and stops at a space.

Conclusion

Teradata’s TRIM function is a versatile tool for enhancing database quality. Mastering it helps ensure clean data for analysis and reporting.

Deciding what to change in your data platform?

Compare optimisation, modernisation and migration against your workload and business requirements.

Explore independent platform assessments

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Follow DWHPro in Google to see our articles more often in Search.

Leave a Comment

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Protected by Security by CleanTalk and CleanTalk Anti-Spam