SQL Functions   «Prev  Next»

Lesson 6 SQL TRIM function
Objective Overview of the SQL TRIM Function

Overview of SQL TRIM Function

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:

FROM Authors

FROM Authors

or even:
FROM Authors

TRIM removes Spaces from both ends

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.

Trimming Characters with TRIM()

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.

See Figure 4.6 for the result.
'<' || ' 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>