In this quick database tutorial, database administrators and developers learn how to use the MySQL LENGTH and TRIM 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 data element.
Consider the following STUDENT table, created with MySQL and the CREATE function:
CREATE TABLE `STUDENT` (` ID` INT (11) NOT NULL, `FIRST NAME` TEXT NOT NULL,) ENGINE = InnoDB;
To insert data into our table we can use the MySQL INSERT command as shown below:
INSERT IN `STUDENT` (` ID`, `VIRSTNAME`) VALUES (1, ‘Steven’); INSERT IN `STUDENT` (` ID`, `FIRSTNAME`) VALUES (2, ‘Randy’);
This code creates a column called FIRSTNAME and then assigns the values Steven and Randy to it. Close observers will notice that Randy has a space at the end, which would be a bad thing in most cases. Below is a MySQL code that counts the character length of all the values in the FIRSTNAME column from the STUDENT database.
LENGTH (FIRST NAME) SELECT LENGTH BY STUDENT
This results in the following output:
+ ——- + | LENGTH | + ——- + | 6 | | 6 | + ——- +
Notice that the second result is six characters, even though the Randy value is only five characters long. This is because of the extra space we mentioned earlier. To fix this problem – 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 (FIRST NAME)) TRIMMED_LENGTH BY STUDENT
Now if we run our query and syntax, we get the following results:
+ ——————————— + | TRIMMED_LENGTHLENGTH | + ——————————— + | 6 | | 5 | + ———————— +
Here we can see that the second value is only five characters long because TRIM has removed one of the characters.
