How to Trim Strings in PostgreSQL

The trim() function removes specified characters or spaces from a string. You can specify to trim from only the start or end of the string, or trim from both. This is best explained using examples.

You can trim "1" from start of the string:

select trim(leading '1' from '111hello111');

 ltrim   
----------
 hello111

You can trim "1" from the end of the string:

select trim(trailing '1' from '111hello111211');

 rtrim     
--------------
 111hello1112

Note: the 1's before the 2 were not trimmed, as they are not the end of the string.

You can specify multiple characters to trim. PostgreSQL will remove any combination of those characters in succession:

select trim(both 'abc' from 'abcbabccchellocbaabc');

 btrim
-------
 hello

PostgreSQL also supports the non-standard syntax of specifying which characters to remove as the last parameter, separated by a comma:

select trim(both 'abcbabccchellocbaabc','abc');

 btrim
-------
 hello

Not specifying where to trim from has the same result as "both":

 select trim('1' from '111hello111');

 btrim
-------
 hello

Not specifying what character to trim will result in trimming spaces:

select trim('    remove spaces from both sides    ');

 btrim             
-------------------------------
 remove spaces from both sides

rtrim() and ltrim() are special versions that only remove trailing and leading characters, respectively. These functions only accept parameters separated by commas:

select ltrim('zzzyclean this up', 'xyz');

 ltrim     
---------------
 clean this up
select rtrim('again not specifying what to trim removes spaces   ');

 rtrim                       
--------------------------------------------------
 again not specifying what to trim removes spaces

Previous

Next

Ready for a modern SQL editor?