SQL TRIM() function

Last update on April 20 2024 12:34:49 (UTC/GMT +8 hours)

TRIM() function

The SQL TRIM() removes leading and trailing characters(or both) from a character string.

Syntax:

TRIM( [ [{LEADING | TRAILING | BOTH}] [removal_char]
FROM ]   target_string    [COLLATE collation_name])

PostgreSQL and Oracle

All of above platforms support the SQL syntax of TRIM().

Parameters:

Name	Description
LEADING	Right most position of a string.
TRAILING	Left most position of a string.
BOTH	Right and left most position of a string.
removal_char	Character to be removed.
target_string	String on which the action will take place.
collation_name	Name of the collation to be applied to the expression.

MySQL Syntax:

TRIM([{BOTH | LEADING | TRAILING} [remstr]
FROM] str); TRIM([remstr FROM] str)

Visual Presentation :

Application of TRIM()

In the subsequent pages, we have discussed how to apply TRIM() with various SQL clauses. we have used Oracle 10g Express Edition.

Example:

To remove the right most '1' from '1234567896541' from the DUAL table, the following SQL statement can be used :


-- This SQL query trims trailing characters from a given string and returns the result with an alias.
-- SELECT statement begins
SELECT 
    TRIM(TRAILING '1' FROM 1234567896541) -- Remove trailing occurrences of '1' from the string '1234567896541'
    AS TRAILING_TRIM -- Alias the result of the trailing trim operation as 'TRAILING_TRIM'
FROM 
    dual; -- Dual is a dummy table in Oracle, used here as a placeholder since we're not actually querying any table

Explanation:

This SQL code utilizes a SELECT statement to demonstrate the use of the TRIM function with the TRAILING keyword.
The TRIM function removes leading and trailing characters from a string.
In this specific query, the TRAILING keyword specifies that only trailing occurrences of the specified character should be removed.
The character '1' is specified as the character to be trimmed from the end of the string.
The string '1234567896541' is provided as the input string.
The result of the trailing trim operation is assigned an alias 'TRAILING_TRIM'.
The query is executed against the 'dual' table, which is a system-generated table in Oracle. It's commonly used as a placeholder for single-row queries or expressions.

Output:

TRAILING_TRIM
------------
123456789654

SQL TRIM() with trailing character

Sample table: customer

+-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+  
|CUST_CODE  | CUST_NAME   | CUST_CITY   | WORKING_AREA | CUST_COUNTRY | GRADE | OPENING_AMT | RECEIVE_AMT | PAYMENT_AMT |OUTSTANDING_AMT| PHONE_NO     | AGENT_CODE |
+-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+
| C00013    | Holmes      | London      | London       | UK           |     2 |     6000.00 |     5000.00 |     7000.00 |       4000.00 | BBBBBBB      | A003       |
| C00001    | Micheal     | New York    | New York     | USA          |     2 |     3000.00 |     5000.00 |     2000.00 |       6000.00 | CCCCCCC      | A008       |
| C00020    | Albert      | New York    | New York     | USA          |     3 |     5000.00 |     7000.00 |     6000.00 |       6000.00 | BBBBSBB      | A008       |
| C00025    | Ravindran   | Bangalore   | Bangalore    | India        |     2 |     5000.00 |     7000.00 |     4000.00 |       8000.00 | AVAVAVA      | A011       |
| C00024    | Cook        | London      | London       | UK           |     2 |     4000.00 |     9000.00 |     7000.00 |       6000.00 | FSDDSDF      | A006       |
| C00015    | Stuart      | London      | London       | UK           |     1 |     6000.00 |     8000.00 |     3000.00 |      11000.00 | GFSGERS      | A003       |
| C00002    | Bolt        | New York    | New York     | USA          |     3 |     5000.00 |     7000.00 |     9000.00 |       3000.00 | DDNRDRH      | A008       |
| C00018    | Fleming     | Brisban     | Brisban      | Australia    |     2 |     7000.00 |     7000.00 |     9000.00 |       5000.00 | NHBGVFC      | A005       |
| C00021    | Jacks       | Brisban     | Brisban      | Australia    |     1 |     7000.00 |     7000.00 |     7000.00 |       7000.00 | WERTGDF      | A005       |
| C00019    | Yearannaidu | Chennai     | Chennai      | India        |     1 |     8000.00 |     7000.00 |     7000.00 |       8000.00 | ZZZZBFV      | A010       |
| C00005    | Sasikant    | Mumbai      | Mumbai       | India        |     1 |     7000.00 |    11000.00 |     7000.00 |      11000.00 | 147-25896312 | A002       |
| C00007    | Ramanathan  | Chennai     | Chennai      | India        |     1 |     7000.00 |    11000.00 |     9000.00 |       9000.00 | GHRDWSD      | A010       |
| C00022    | Avinash     | Mumbai      | Mumbai       | India        |     2 |     7000.00 |    11000.00 |     9000.00 |       9000.00 | 113-12345678 | A002       |
| C00004    | Winston     | Brisban     | Brisban      | Australia    |     1 |     5000.00 |     8000.00 |     7000.00 |       6000.00 | AAAAAAA      | A005       |
| C00023    | Karl        | London      | London       | UK           |     0 |     4000.00 |     6000.00 |     7000.00 |       3000.00 | AAAABAA      | A006       |
| C00006    | Shilton     | Torento     | Torento      | Canada       |     1 |    10000.00 |     7000.00 |     6000.00 |      11000.00 | DDDDDDD      | A004       |
| C00010    | Charles     | Hampshair   | Hampshair    | UK           |     3 |     6000.00 |     4000.00 |     5000.00 |       5000.00 | MMMMMMM      | A009       |
| C00017    | Srinivas    | Bangalore   | Bangalore    | India        |     2 |     8000.00 |     4000.00 |     3000.00 |       9000.00 | AAAAAAB      | A007       |
| C00012    | Steven      | San Jose    | San Jose     | USA          |     1 |     5000.00 |     7000.00 |     9000.00 |       3000.00 | KRFYGJK      | A012       |
| C00008    | Karolina    | Torento     | Torento      | Canada       |     1 |     7000.00 |     7000.00 |     9000.00 |       5000.00 | HJKORED      | A004       |
| C00003    | Martin      | Torento     | Torento      | Canada       |     2 |     8000.00 |     7000.00 |     7000.00 |       8000.00 | MJYURFD      | A004       |
| C00009    | Ramesh      | Mumbai      | Mumbai       | India        |     3 |     8000.00 |     7000.00 |     3000.00 |      12000.00 | Phone No     | A002       |
| C00014    | Rangarappa  | Bangalore   | Bangalore    | India        |     2 |     8000.00 |    11000.00 |     7000.00 |      12000.00 | AAAATGF      | A001       |
| C00016    | Venkatpati  | Bangalore   | Bangalore    | India        |     2 |     8000.00 |    11000.00 |     7000.00 |      12000.00 | JRTVFDD      | A007       |
| C00011    | Sundariya   | Chennai     | Chennai      | India        |     3 |     7000.00 |    11000.00 |     7000.00 |      11000.00 | PPHGRTS      | A010       |
+-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+

To remove right most 'n' from the 'cust_name' column of 'customer' table, the following SQL statement can be used :


-- This SQL query trims trailing characters from a column in the 'customer' table and returns the result.
-- SELECT statement begins
SELECT 
    TRIM(TRAILING 'n' FROM cust_name) -- Remove trailing occurrences of 'n' from the column 'cust_name' in the 'customer' table
FROM 
    customer; -- Query data from the 'customer' table

Explanation:

This SQL code is a SELECT statement that demonstrates the use of the TRIM function with the TRAILING keyword to remove trailing characters from a column in a table.
The TRIM function is applied to the 'cust_name' column in the 'customer' table.
The TRAILING keyword specifies that only trailing occurrences of the specified character ('n' in this case) should be removed.
The result of the trailing trim operation is returned as part of the query result set.
The query is executed against the 'customer' table, which presumably contains customer data, and retrieves the trimmed 'cust_name' column.

Output

TRIM(TRAILING'N'FROMCUST_NAME)
-----------------------------------
Holmes
Micheal
Albert
Ravindra
Cook
Stuart
Bolt
Fleming
Jacks
Yearannaidu
Sasikant
Ramanatha
Avinash
Winsto
Karl
Shilto
Charles
Srinivas
Steve
Karolina
Marti
Ramesh
Rangarappa
Venkatpati
Sundariya

SQL TRIM() with leading character

To remove the left most '1' from the string '1234567896541' from the DUAL table, the following SQL statement can be used :

SQL Code:


-- This SQL query trims leading characters from a given string and returns the result with an alias.
-- SELECT statement begins
SELECT 
    TRIM(LEADING '1' FROM 1234567896541) -- Remove leading occurrences of '1' from the string '1234567896541'
    AS LEADING_TRIM -- Alias the result of the leading trim operation as 'LEADING_TRIM'
FROM 
    dual; -- Dual is a dummy table in Oracle, used here as a placeholder since we're not actually querying any table

Explanation:

This SQL code utilizes a SELECT statement to demonstrate the use of the TRIM function with the LEADING keyword.
The TRIM function removes leading and trailing characters from a string.
In this specific query, the LEADING keyword specifies that only leading occurrences of the specified character should be removed.
The character '1' is specified as the character to be trimmed from the beginning of the string.
The string '1234567896541' is provided as the input string.
The result of the leading trim operation is assigned an alias 'LEADING_TRIM'.
The query operates on the 'dual' table, a system-generated table in Oracle often used as a placeholder for single-row queries or expressions.

In the above example, the "LEADING_TRIM" is a column alias which will come as a column heading to the output.

Output:

LEADING_TRIM
------------
234567896541

SQL TRIM() on column with leading character

Sample table: customer

+-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+  
|CUST_CODE  | CUST_NAME   | CUST_CITY   | WORKING_AREA | CUST_COUNTRY | GRADE | OPENING_AMT | RECEIVE_AMT | PAYMENT_AMT |OUTSTANDING_AMT| PHONE_NO     | AGENT_CODE |
+-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+
| C00013    | Holmes      | London      | London       | UK           |     2 |     6000.00 |     5000.00 |     7000.00 |       4000.00 | BBBBBBB      | A003       |
| C00001    | Micheal     | New York    | New York     | USA          |     2 |     3000.00 |     5000.00 |     2000.00 |       6000.00 | CCCCCCC      | A008       |
| C00020    | Albert      | New York    | New York     | USA          |     3 |     5000.00 |     7000.00 |     6000.00 |       6000.00 | BBBBSBB      | A008       |
| C00025    | Ravindran   | Bangalore   | Bangalore    | India        |     2 |     5000.00 |     7000.00 |     4000.00 |       8000.00 | AVAVAVA      | A011       |
| C00024    | Cook        | London      | London       | UK           |     2 |     4000.00 |     9000.00 |     7000.00 |       6000.00 | FSDDSDF      | A006       |
| C00015    | Stuart      | London      | London       | UK           |     1 |     6000.00 |     8000.00 |     3000.00 |      11000.00 | GFSGERS      | A003       |
| C00002    | Bolt        | New York    | New York     | USA          |     3 |     5000.00 |     7000.00 |     9000.00 |       3000.00 | DDNRDRH      | A008       |
| C00018    | Fleming     | Brisban     | Brisban      | Australia    |     2 |     7000.00 |     7000.00 |     9000.00 |       5000.00 | NHBGVFC      | A005       |
| C00021    | Jacks       | Brisban     | Brisban      | Australia    |     1 |     7000.00 |     7000.00 |     7000.00 |       7000.00 | WERTGDF      | A005       |
| C00019    | Yearannaidu | Chennai     | Chennai      | India        |     1 |     8000.00 |     7000.00 |     7000.00 |       8000.00 | ZZZZBFV      | A010       |
| C00005    | Sasikant    | Mumbai      | Mumbai       | India        |     1 |     7000.00 |    11000.00 |     7000.00 |      11000.00 | 147-25896312 | A002       |
| C00007    | Ramanathan  | Chennai     | Chennai      | India        |     1 |     7000.00 |    11000.00 |     9000.00 |       9000.00 | GHRDWSD      | A010       |
| C00022    | Avinash     | Mumbai      | Mumbai       | India        |     2 |     7000.00 |    11000.00 |     9000.00 |       9000.00 | 113-12345678 | A002       |
| C00004    | Winston     | Brisban     | Brisban      | Australia    |     1 |     5000.00 |     8000.00 |     7000.00 |       6000.00 | AAAAAAA      | A005       |
| C00023    | Karl        | London      | London       | UK           |     0 |     4000.00 |     6000.00 |     7000.00 |       3000.00 | AAAABAA      | A006       |
| C00006    | Shilton     | Torento     | Torento      | Canada       |     1 |    10000.00 |     7000.00 |     6000.00 |      11000.00 | DDDDDDD      | A004       |
| C00010    | Charles     | Hampshair   | Hampshair    | UK           |     3 |     6000.00 |     4000.00 |     5000.00 |       5000.00 | MMMMMMM      | A009       |
| C00017    | Srinivas    | Bangalore   | Bangalore    | India        |     2 |     8000.00 |     4000.00 |     3000.00 |       9000.00 | AAAAAAB      | A007       |
| C00012    | Steven      | San Jose    | San Jose     | USA          |     1 |     5000.00 |     7000.00 |     9000.00 |       3000.00 | KRFYGJK      | A012       |
| C00008    | Karolina    | Torento     | Torento      | Canada       |     1 |     7000.00 |     7000.00 |     9000.00 |       5000.00 | HJKORED      | A004       |
| C00003    | Martin      | Torento     | Torento      | Canada       |     2 |     8000.00 |     7000.00 |     7000.00 |       8000.00 | MJYURFD      | A004       |
| C00009    | Ramesh      | Mumbai      | Mumbai       | India        |     3 |     8000.00 |     7000.00 |     3000.00 |      12000.00 | Phone No     | A002       |
| C00014    | Rangarappa  | Bangalore   | Bangalore    | India        |     2 |     8000.00 |    11000.00 |     7000.00 |      12000.00 | AAAATGF      | A001       |
| C00016    | Venkatpati  | Bangalore   | Bangalore    | India        |     2 |     8000.00 |    11000.00 |     7000.00 |      12000.00 | JRTVFDD      | A007       |
| C00011    | Sundariya   | Chennai     | Chennai      | India        |     3 |     7000.00 |    11000.00 |     7000.00 |      11000.00 | PPHGRTS      | A010       |
+-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+

To remove left most 'S' from the 'cust_name' column of the 'customer' table, the following SQL statement can be used :


-- This SQL query trims leading characters from a column in the 'customer' table and returns the result.
-- SELECT statement begins
SELECT 
    TRIM(LEADING 'S' FROM cust_name) -- Remove leading occurrences of 'S' from the column 'cust_name' in the 'customer' table
FROM 
    customer; -- Query data from the 'customer' table

Explanation:

This SQL code is a SELECT statement that demonstrates the use of the TRIM function with the LEADING keyword.
The TRIM function is applied to the 'cust_name' column in the 'customer' table.
The LEADING keyword specifies that only leading occurrences of the specified character ('S' in this case) should be removed.
The result of the leading trim operation is returned as part of the query result set.
The query is executed against the 'customer' table, which presumably contains customer data, and retrieves the trimmed 'cust_name' column.

Output:

TRIM(LEADING'S'FROMCUST_NAME)
--------------------------------
Holmes
Micheal
Albert
Ravindran
Cook
tuart
Bolt
Fleming
Jacks
Yearannaidu
asikant
Ramanathan
Avinash
Winston
Karl
hilton
Charles
rinivas
teven
Karolina
Martin
Ramesh
Rangarappa
Venkatpati
undariya

SQL TRIM() from both side

To remove the left and right most '1' from the string '1234567896541' from the DUAL table, the following SQL statement can be used:

SQL Code:


-- This SQL query trims specified characters from both the beginning and end of a given string and returns the result with an alias.
-- SELECT statement begins
SELECT 
    TRIM('1' FROM 1234567896541) -- Remove occurrences of '1' from both the beginning and end of the string '1234567896541'
    AS BOTH_TRIM -- Alias the result of the trim operation as 'BOTH_TRIM'
FROM 
    dual; -- Dual is a dummy table in Oracle, used here as a placeholder since we're not actually querying any table

Explanation:

This SQL code is a SELECT statement that demonstrates the use of the TRIM function to remove specified characters from both the beginning and end of a string.
The TRIM function is applied to the string '1234567896541'.
The character '1' is specified as the character to be trimmed from both ends of the string.
The result of the trim operation is assigned an alias 'BOTH_TRIM'.
The query operates on the 'dual' table, a system-generated table in Oracle frequently utilized as a placeholder for single-row queries or expressions.

Output

BOTH_TRIM
-----------
23456789654

See our Model Database

Here is a new document which is a collection of questions with short and simple answers, useful for learning SQL as well as for interviews.

Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.

Previous: UPPER
Next: TRANSLATE