MySQL TRIM() function

In the past life was easy in MySQL. Both CHAR and VARCHAR types meant the same, only being difference in the sense of fixed or dynamic row length used. Trailing spaces were removed in both cases.

With MySQL 5.0 however things changed so now VARCHAR keeps trailing spaces while CHAR columns do not any more. Well in reality CHAR columns are padded to full length with spaces but it is invisible as those trailing spaces are removed upon retrieval. This is something you need to watch both upgrading to MySQL 5.0 as well as designing your applications – you should keep into account if you mind trailing spaces stored choosing VARCHAR vs CHAR in addition to fixed length vs dynamic level rows and space spent for column size counter.

There is more fun stuff with trailing spaces. When comparison is done trailing spaces are always removed, even if VARCHAR column is used which is pretty counterintuitive. So “a “=”a”=”a ” for all textual column types – CHAR, VARCHAR, TEXT. BLOB is exception it will preserve trailing spaces and use them in comparison.

Examples:

mysql> SELECT TRIM('  bar   ');
        -> 'bar'
mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx');
        -> 'barxxx'
mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx');
        -> 'bar'
mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz');
        -> 'barx'

Syntax:

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

Definition:Remove leading and trailing spaces.

 Name     Description 
 BOTH    Indicates that prefixes from both left and right are to be removed. 
 LEADING  Indicates that only leading prefixes are to be removed. 
 TRAILING Indicates that only trailing prefixes are to be removed. 
 remstr   The string to be removed. 
 FROM    Keyword 
 str    The actual string from where remstr is to be removed. 
 

TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM ] str) - See more at: http://www.w3resource.com/mysql/string-functions/mysql-trim-function.php#sthash.5NUQ1x83.dpufTRIM([{BOTH | LEADING | TRAILING} [remstr] FROM ] str) - See more at: http://www.w3resource.com/mysql/string-functions/mysql-trim-function.php#sthash.5NUQ1x83.dpufTRIM([{BOTH | LEADING | TRAILING} [remstr] FROM ] str) - See more at: http://www.w3resource.com/mysql/string-functions/mysql-trim-function.php#sthash.5NUQ1x83.dpufTRIM([{BOTH | LEADING | TRAILING} [remstr] FROM ] str) - See more at: http://www.w3resource.com/mysql/string-functions/mysql-trim-function.php#sthash.5NUQ1x83.dpufTRIM([{BOTH | LEADING | TRAILING} [remstr] FROM ] str) - See more at: http://www.w3resource.com/mysql/string-functions/mysql-trim-function.php#sthash.5NUQ1x83.dpuf