In this database programming tutorial, developers and database administrators will learn the syntax for getting the length of text in a column using MySQL.
Look at the STOCKPRICE table:
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 IN ‘STOCKPRICE’ (‘ID’, ‘NAME’, ‘PRICE’) VALUES (1, ‘MM Corp’, 25); INSERT IN `STOCKPRICE` (` ID`, `NAME`,` PRICE`) VALUES (2, ‘NN Corp’, 35); INSERT IN `STOCKPRICE` (` ID`, `NAME`,` PRICE`) VALUES (3, ‘HINT Co’, 30);
Now that we have created a new database and filled it with sample data, we determine the length of the values in the NAME column:
SELECT LENGTH (NAME) LENGTH FROM STOCK PRICE
This query results in the following output:
+ ——– + | LENGTH | + ——- + | 7 | | 8 | | 7 | + ——- +
We can use TRIM to get a different result in case 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 STOCK PRICE
This query results in the following output:
+ ——- + | LENGTH | + ——- + | 7 | | 7 | | 7 | + ——- +
