How to Get the Average String Length of a Column in MySQL

Published Sep 3, 2022  ∙  Updated Sep 4, 2022

Let’s see how we can retrieve the average string length of a column in MySQL.

Average string length using AVG() and LENGTH()

We can use the aggregate AVG() function with LENGTH() to obtain the average length of a MySQL column.

SELECT AVG(LENGTH(colName)) 
FROM tableName;

We can, of course, replace AVG() with any aggregate function (e.g. MIN(), MAX(), etc).

Let’s walk through a quick example.

Example scenario: average string length

First, we’ll create a table.

CREATE TABLE tableName (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  colName VARCHAR(256)
);

Then, we’ll insert some values.

INSERT INTO tableName(colName) VALUES('1');     -- Length = 1
INSERT INTO tableName(colName) VALUES('12345'); -- Length = 5

Finally, we’ll run our query.

SELECT AVG(LENGTH(colName)) 
FROM tableName;

And get the output: 3.

Read up on how to obtain all the keys in a MySQL JSON object.