Overview of SQL TRIM Function in SQL 2023

The SQL TRIM() function removes unwanted characters from the beginning, the end, or both ends of a string. In practice it is most often used to remove leading and trailing spaces, but the SQL standard also allows you to trim other characters such as zeros, asterisks, or punctuation.

Standard SQL TRIM Syntax (SQL:2023)

The SQL:2023 standard defines TRIM() using the following syntax:

TRIM( [ [ LEADING | TRAILING | BOTH ] [ trim_character ] FROM ] input_string )

Key elements:

• LEADING – Trim only from the start of the string.
• TRAILING – Trim only from the end.
• BOTH – Trim from both start and end (this is the default).
• trim_character – The character to remove. The default is a space character ' '.
• input_string – The source string you are trimming.

If you omit the LEADING|TRAILING|BOTH phrase and the trim_character, the function trims spaces from both ends of input_string.

SQL Data Analytics

Basic TRIM Examples

1. Trim spaces from both sides (default):
   

   SELECT TRIM('   Oracle 23c   ');
   -- Result: 'Oracle 23c'

2. Trim a specific character (for example, 'x') from both ends:
   

   SELECT TRIM(BOTH 'x' FROM 'xxxData123xxx');
   -- Result: 'Data123'

3. Trim leading characters only:
   

   SELECT TRIM(LEADING '0' FROM '00012345');
   -- Result: '12345'

4. Trim trailing characters only:
   

   SELECT TRIM(TRAILING '9' FROM '789999');
   -- Result: '789'

Modern relational database engines implement TRIM() according to, or very close to, the SQL standard:

• Oracle and PostgreSQL support the standard TRIM() syntax with LEADING, TRAILING, and BOTH.
• MySQL, MariaDB, and SQLite support both a basic TRIM() form and the SQL-standard variant.
• SQL Server supports TRIM() and also provides the older LTRIM() and RTRIM() functions.

Best Practices

• Use TRIM() to clean up user input before storing or comparing values.
• Trim values in WHERE clauses when you suspect that padding may cause false mismatches.
• Use TRIM() to remove padding from fixed-length CHAR columns.
• Prefer the standard TRIM() syntax in new code for portability, and fall back to vendor functions only when necessary.

The core behavior of TRIM() is that it removes characters only from the edges of a string. It will not remove characters from the middle of the string. For example, internal spaces between words are preserved.

Many database engines also provide LTRIM() and RTRIM() for trimming whitespace specifically from the left or right:

SELECT LTRIM(au_Lname)
FROM   Authors;

or:

SELECT RTRIM(au_Lname)
FROM   Authors;

or even:

SELECT LTRIM(RTRIM(au_Lname))
FROM   Authors;

In environments that support standard TRIM(), the last pattern is essentially equivalent to:

SELECT TRIM(au_Lname)
FROM   Authors;

New code should generally prefer TRIM() for clarity and portability, while recognizing that LTRIM() and RTRIM() remain common in older scripts.

When you use TRIM() without specifying LEADING, TRAILING, or a custom trim_character, the function removes spaces from both ends of the string in a single call. This is often the most convenient and readable form:

SELECT TRIM('   Data Value   ')
FROM   dual;

Conceptually, this is similar to calling RTRIM() first and LTRIM() second. A function can operate on the output of another function, so you will frequently see nested calls like LTRIM(RTRIM(column_name)) in legacy code.

Some database engines historically supported only TRIM() or only LTRIM() and RTRIM(); modern releases generally support all three. Always check your specific DBMS documentation if you are working with an older version.

You can use the standard form of TRIM() to remove characters other than spaces. Important characteristics include:

1. You can trim leading characters, trailing characters, or both. You cannot use TRIM() to remove characters from inside a string; it only affects the edges.
2. By default, TRIM() removes spaces, but you can specify any single character to strip off, such as leading and trailing zeros, hash marks, or asterisks.
3. TRIM() is commonly used to format result sets and to make string comparisons more robust in WHERE clauses.
4. TRIM() is especially useful with CHAR columns, where the DBMS automatically pads values with spaces to a fixed length.
5. Trimming has no effect on empty strings; there is nothing to remove. In systems such as Oracle, an unquoted empty string in SQL is treated as NULL, and trimming a NULL value also results in NULL.
6. In standard SQL, if the input expression is NULL, TRIM() returns NULL. This is consistent with the way most scalar functions propagate NULL.

Listing 4.6 shows a simple example that trims leading, trailing, and both leading and trailing spaces from the string ' AAA '. The < and > characters are used to mark the boundaries of each value so that you can clearly see what has been removed.

SELECT
  '<' || ' AAA ' || '>'                  AS "Untrimmed",
  '<' || TRIM(LEADING  FROM ' AAA ') || '>' AS "Leading",
  '<' || TRIM(TRAILING FROM ' AAA ') || '>' AS "Trailing",
  '<' || TRIM(           ' AAA ' ) || '>'    AS "Both"
FROM dual;

Result:

Untrimmed Leading    Trailing   Both
--------- ---------- ---------- -----
< AAA >  <AAA >     < AAA>     <AAA>

Notice that only the spaces at the beginning and end of the string are removed. Any spaces between non-space characters would remain untouched.