w3resource logo


>Sql trim

SQL TRIM() function

rating Sql trim function has average rating 7 out of 10. Total 34 users rated.

<<PreviousNext>>

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

Sql trim()

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

Sql trim() with trailing character

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

Sql trim() on column with leading character

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

Sql trim() on column with leading character

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

Sql trim() from both side

Outputs of the said SQL statement shown here is taken by using Oracle Database 10g Express Edition.

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