SQL Tutorial SQL Advanced SQL Database SQL References

MySQL TRIM() Function



The MySQL TRIM() function removes all specified characters either from the beginning or the end of a string.

Syntax

TRIM( [LEADING | TRAILING | BOTH]  [trim_string FROM]  string)

Parameters

LEADING Optional. Specify to remove the trim_string from the front of the string.
TRAILING Optional. Specify to remove the trim_string from the end of the string.
BOTH Optional. Specify to remove the trim_string from the front and end of the string.
trim_string Optional. Specify the string to be trimmed from string. If omitted, spaces will be removed.
string Required. Specify the string to trim.

Return Value

Returns the trimmed version of the specified string.

Example 1:

The example below shows the usage of TRIM() function.

mysql> SELECT TRIM('  SQL Tutorial    ');
Result: 'SQL Tutorial'

mysql> SELECT TRIM(BOTH ' ' FROM '  SQL Tutorial    ');
Result: 'SQL Tutorial'

mysql> SELECT TRIM(TRAILING ' ' FROM '  SQL Tutorial    ');
Result: '  SQL Tutorial'

mysql> SELECT TRIM(LEADING ' ' FROM '  SQL Tutorial    ');
Result: 'SQL Tutorial    '

mysql> SELECT TRIM(' ' FROM '  SQL Tutorial    ');
Result: 'SQL Tutorial'

mysql> SELECT TRIM(LEADING '0' FROM '000123');
Result: '123'

mysql> SELECT TRIM(TRAILING '1' FROM 'John1');
Result: 'John'

mysql> SELECT TRIM(TRAILING '123' FROM 'John123');
Result: 'John'

mysql> SELECT TRIM(BOTH '123' FROM '123John123');
Result: 'John'

Example 2:

Consider a database table called Employee. When the following INSERT statements are executed, the Name column will contain records with leading and trailing spaces.

INSERT INTO Employee VALUES ('        John   ', 'London', 3000);
INSERT INTO Employee VALUES ('        Marry   ', 'New York', 2750);
INSERT INTO Employee VALUES ('      Jo    ', 'Paris', 2800);
INSERT INTO Employee VALUES ('      Kim    ', 'Amsterdam', 3100);

-- see the result
SELECT * FROM Employee;

The query will produce the following result:

NameCitySalary
        John   London3000
        Marry   New York2750
      Jo    Paris2800
      Kim    Amsterdam3100

To remove the leading and trailing spaces from the Name column of the Employee table, the following query can be used:

UPDATE Employee SET Name = TRIM(Name);

-- see the result
SELECT * FROM Employee;

This will produce the following result:

NameCitySalary
JohnLondon3000
MarryNew York2750
JoParis2800
KimAmsterdam3100

❮ MySQL Functions