Overview of SQL TRIM Function

The TRIM() function in SQL is used to remove specified prefixes and suffixes from a string. By default, if no characters are specified, it removes white spaces from both ends (leading and trailing) of the string.
Let's consider the following usage of the TRIM() function:

SELECT TRIM('  Hello World  ');

This statement will output: Hello World. Note that the leading and trailing spaces are removed.

However, TRIM() is not restricted to removing spaces; you can specify other characters to remove as well. For example:

SELECT TRIM('H' FROM 'Hello World');

This will remove the leading and trailing 'H' characters, resulting in ello World.
Now, regarding the LTRIM() and RTRIM() functions: these are more specific variants of the TRIM() function. The LTRIM() function only removes leading spaces (spaces at the start of a string), and the RTRIM() function only removes trailing spaces (spaces at the end of a string). Let's consider their usage:

SELECT LTRIM('  Hello World');

This statement will output: Hello World (with leading spaces removed).

SELECT RTRIM('Hello World  ');

This statement will output: Hello World (with trailing spaces removed).
Remember, while TRIM() can remove other characters and can work from both ends of the string, LTRIM() and RTRIM() are specifically designed to handle spaces and they work on one end of the string only. It is important to note that not all SQL dialects support LTRIM() and RTRIM(), so always consult the specific SQL platform's documentation to ensure proper usage.

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

SELECT RTRIM(au_Lname) 
FROM Authors

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

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