Overview of SQL TRIM Function in SQL 2023

The `TRIM()` function in SQL:2023 (the latest SQL standard) is used to remove unwanted characters from the beginning, end, or both ends of a string. SQL:2023 enhances the function to support a more explicit and flexible syntax than some earlier database-specific implementations.
🔹 Basic Purpose
To remove leading, trailing, or both leading and trailing characters (typically spaces) from a string.
🔹 SQL:2023 Standard Syntax

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

✳️ Parameters:

• LEADING: Trims the specified character from the start.
• TRAILING: Trims the character from the end.
• BOTH: Trims the character from both start and end (default).
• trim_character: The character to remove (default is space ' ').
• input_string: The string to operate on.

✅ Examples

1. Trim spaces from both sides (default):
   

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

2. Trim a specific character (e.g., 'x') from both ends:
   

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

3. Trim from the beginning only:
   

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

4. Trim from the end only:

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

• Oracle and PostgreSQL support the SQL:2023 syntax.
• SQL Server only supports LTRIM() and RTRIM() separately.
• MySQL, MariaDB, and SQLite support both basic TRIM() and the SQL-standard variant.

🧠 Best Practices
Use `TRIM()` when:

• Cleaning up user input.
• Comparing strings (to avoid false mismatches due to padding).
• Removing padding characters from fixed-length fields (e.g., CHAR).

The TRIM function will remove all spaces from the ends of the string. Note that it will not remove spaces from inside the string, only from each end.
This is a really helpful cleanup function, and you will find that you often use it in conjunction with other functions, like the concatenation of strings.
TRIM is easy to use. You simply call the function with the value you would like to have trimmed. You have two different options, left and right TRIM. To use these, you will use LTRIM and RTRIM, respectively:

SELECT LTRIM(au_Lname) 
FROM Authors

or:

SELECT RTRIM(au_Lname) 
FROM Authors

or even:

SELECT LTRIM(RTRIM(au_Lname)) 
FROM Authors

This final option is interesting because it will trim both ends of the string, removing spaces from each side.
It is also important to understand that a function can work with the results of another function. In this case, first the string is trimmed from the right, then from the left.
TRIM is another one of the functions that may be implemented differently on your database engine. You may find that your engine only supports TRIM, not LTRIM and RTRIM. If this is the case, TRIM will generally remove spaces from both ends of the string at once.

Use the function TRIM() to remove unwanted characters from the ends of a string. The important characteristics of the function are:

1. You can trim leading characters, trailing characters, or both. (You cannot use TRIM() to remove characters from within a string.)
2. By default, TRIM() trims spaces, but you can strip off any unwanted characters, such as leading and trailing zeros or asterisks.
3. TRIM() typically is used to format results and make comparisons in a WHERE clause.
4. TRIM() is useful for trimming trailing spaces from CHAR values.
   "Character String Type" DBMSs add spaces automatically to the end of CHAR values to create strings of exactly a specified length.
5. Trimming has no effect on empty strings.
6. If any argument is null, TRIM() returns null. However, this does not hold true for Oracle.

Listing 4.6 This query strips leading, trailing, and both leading and trailing spaces from the string " AAA ". The < and > characters show the extent of the trimmed strings.

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

Result:

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