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.