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

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

Developer.com content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.

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.

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Latest Posts

Related Stories