w3resource logo


>Sql trim

SQL TRIM() function

<<PreviousNext>>

Seondary Nav

Description

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)

Pictorial Presentation :

Sql trim() function 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 :

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 :

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.



<<PreviousNext>>

Looking for some other tutorial?