DatabaseMySQL: How to Get the Length of Text In A Column

MySQL: How to Get the Length of Text In A Column

MySQL Database Tutorials

In this database programming tutorial, developers and database admins will learn the syntax for how to get the length of text in a column using MySQL.

Consider the table STOCKPRICE:

CREATE TABLE `STOCKPRICE` (
	`ID` INT(11) NOT NULL,
	`NAME` TEXT NOT NULL COLLATE 'utf8_bin',
	`PRICE` INT(11) NOT NULL
)
COLLATE='utf8_bin'
ENGINE=InnoDB
;

Fill the newly created database with sample data, as shown below:

INSERT INTO `STOCKPRICE` (`ID`, `NAME`, `PRICE`) VALUES (1, 'MM Corp', 25);
INSERT INTO `STOCKPRICE` (`ID`, `NAME`, `PRICE`) VALUES (2, 'NN Corp ', 35);
INSERT INTO `STOCKPRICE` (`ID`, `NAME`, `PRICE`) VALUES (3, 'HINT Co', 30);

Now that we have created a new database and populated it with sample data, let’s find the length of the values in the NAME column:

SELECT LENGTH(NAME) LENGTH FROM STOCKPRICE

This query results in the output below:

+-------+
|LENGTH |
+-------+
|  7    |
|  8    |
|  7    |
+-------+

We can use TRIM to yield a different result in the event that there are extra spaces at the end of each entry; the TRIM statement removes the extra space at the end of an entry:

SELECT LENGTH(TRIM(NAME)) LENGTH FROM STOCKPRICE

This query results in the following output:

+-------+
|LENGTH |
+-------+
|  7    |
|  7    |
|  7    |
+-------+

Read more database programming and administration tutorials.

Latest Posts

Related Stories