How to SELECT the Longest String in a SQLite Column?

Find the longest string in a SQLite column

Let's suppose you have the following SQLite table:

+----+--------------------------+
| id | title                    |
+----+--------------------------+
|  1 | Lorem Ipsum              |
|  2 | Proin ut Ante            |
|  3 | Duis imperdiet eu libero |
|  4 | Curabitur nulla          |
|  5 | Fusce vitae              |
+----+--------------------------+
CREATE TABLE `blog_post` (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title VARCHAR(25) NOT NULL
);

INSERT INTO blog_post(`title`)
VALUES
    ('Lorem Ipsum'),
    ('Proin ut Ante'),
    ('Duis imperdiet eu libero'),
    ('Curabitur nulla'),
    ('Fusce vitae');

To get the column with the longest string in SQLite, you can do either of the following:

Sort Column in Descending Order by Length

You can simply sort the column in descending order by LENGTH and then show only the first result, for example, like so:

SELECT `title`, LENGTH(`title`) count
FROM `blog_post`
ORDER BY LENGTH(`title`) DESC
LIMIT 1;

This would yield the following result:

+--------------------------+-------+
| title                    | count |
+--------------------------+-------+
| Duis imperdiet eu libero | 24    |
+--------------------------+-------+

You can use the same query to show more than one result by increasing the value of the LIMIT clause.

SELECT Column by Maximum Length

You can combine the SQLite MAX() and LENGTH() functions to only select the column row with the longest string, for example, like so:

SELECT `title`, MAX(LENGTH(`title`)) count FROM `blog_post`;

This would yield the following result:

+--------------------------+-------+
| title                    | count |
+--------------------------+-------+
| Duis imperdiet eu libero | 24    |
+--------------------------+-------+

Hope you found this post useful. It was published . Please show your love and support by sharing this post.