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 | +--------------------------+-------+
This post was published by Daniyal Hamid. Daniyal currently works as the Head of Engineering in Germany and has 20+ years of experience in software engineering, design and marketing. Please show your love and support by sharing this post.