MySQL Quick Tip: Using the LENGTH and TRIM Function

In this quick database tutorial, database admins and developers will learn how to use the LENGTH and TRIM MySQL Functions. LENGTH can be used to count the number of characters in a column, while TRIM can be used to remove one or more characters from a column or piece of data.

Using the LENGTH and TRIM Function in MySQL

Considering the following table STUDENT, created with MySQL and the CREATE function:

CREATE TABLE `STUDENT` (
	`ID` INT(11) NOT NULL,
	`FIRSTNAME` TEXT NOT NULL,
)
ENGINE=InnoDB
;

To insert data into our table, we can use the MySQL INSERT command as shown below:

INSERT INTO `STUDENT` (`ID`, `FIRSTNAME`) VALUES (1, 'Steven');
INSERT INTO `STUDENT` (`ID`, `FIRSTNAME`) VALUES (2, 'Randy ');

This code creates a column named FIRSTNAME and then assigns it the values Steven and Randy. Keen observers will note that Randy has a space at the end, which in most cases would be a bad thing. Below is some MySQL code that will count the character length of all of the values in the FIRSTNAME column from the STUDENT database.

SELECT LENGTH(FIRSTNAME) LENGTH FROM STUDENT

This results in the output below:

+-------+
|LENGTH |
+-------+
|  6    |
|  6    |
+-------+

Notice how the second result has six characters, even though the value Randy is only five characters long. This is because of that extra space we mentioned before. To fix this issue – and remove the extra space at the end of Randy, we can use the MySQL TRIM command, as in the following example:

SELECT LENGTH(TRIM(FIRSTNAME)) TRIMMED_LENGTH FROM STUDENT

Now when we run our query and syntax, we get the following results:

+---------------------+
|TRIMMED_LENGTHLENGTH |
+---------------------+
|  6                  |
|  5                  |
+---------------------+

Here, we can see that the second value is now only five characters long, as TRIM removed one of the characters.

Read: MySQL INSERT or REPLACE Commands