SQL TRIM() function
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
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.
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics