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:

MySQL Syntax:

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

Pictorial 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 :

SELECT TRIM(TRAILING '1' FROM 1234567896541) 
AS TRAILING_TRIM 
FROM dual;

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

Output:

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

SQL TRIM() with trailing character

Sample table : customer

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

SELECT TRIM(TRAILING 'n' FROM cust_name) 
FROM customer; 

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:

SELECT TRIM(LEADING '1' FROM 1234567896541 ) 
AS LEADING_TRIM 
FROM dual;

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

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

SELECT TRIM(LEADING 'S' FROM cust_name ) 
FROM customer; 

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:

SELECT TRIM('1' FROM 1234567896541) 
AS BOTH_TRIM 
FROM dual;

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

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