Oracle pl/sql trim function

 

Oracle pl/sql trim function

The trim function removed characters from beginning and/or end of a string in Oracle. Oracle has 3 functions for this:

TRIM

The TRIM function trims specified characters from the left and/or right.
If no characters are specified, the left and right spaces are left out.
Example: trim(' Oradev dot com ') = 'Oradev dot com'.
Another option is:
trim(trailing 'a' from 'aaaabbaaaa') which results in 'aaaabb' or
trim(leading 'a' from 'aaaabbaaaa') which results in 'bbaaaa' or
trim(both 'a' from 'aaaabbaaaa') which results in 'bb'.

select trim('a' from 'aabbaa') from dual which resules in 'bb'

both可以省略

LTRIM

LTRIM removes characters from the left of a string if they are equal to the specified string. Example: ltrim('aaaaaabc','a') = 'bc' If the last parameter is not specified, spaces are removed from the left side.

RTRIM

RTRIM removes characters from the right of a string if they are equal to the specified string. Example: rtrim('bcaaaaaa','a') = 'bc' If the last parameter is not specified, spaces are removed from the right side.

 

remove blanks 去除空格

select trim('         sample       ') from dual;
select rtrim('        sample       ') from dual;
select ltrim('        sample       ') from dual;

 

select translate('123456aabb654321','1234567','0000000') from dual

the result is: 000000aabb000000

http://www.oradev.com/pl_sql_trim.jsp