MySQL query to count longest line in text field

Question

I've some data in a text field which is separated by newline characters. For each row, I'd like to get the length of the longest line in that row.

If for example one row contained the following data

the cat\nsat on the\nmat

then I'd like a query to return 10, the length of the longest line 'sat on the'.

I don't think this can be done by mere sql. You should use programming. — ankur140290, Apr 17 '15 at 12:30
I agree with @Ankur140290 especially as there's no split() function in mysql — pierroz, Apr 17 '15 at 12:32
You can create a user function for that, for example see http://stackoverflow.com/questions/17942508/sql-split-values-to-multiple-rows — AlecTMH, Apr 17 '15 at 12:34

score 2 · Accepted Answer · answered Apr 17 '15 at 12:45

Well here's one idea...

For ease of readability, in this solution I've substituted "\n" with "|".

And remember, just because you 'can', it doesn't follow that you 'should'!

SET @string = "the cat|sat on the|mat";

SELECT * FROM ints;
+---+
| i |
+---+
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
+---+

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(@string,"|",i),"|",-1)x FROM ints;
+------------+
| x          |
+------------+
|            |
| the cat    |
| sat on the |
| mat        |
| mat        |
| mat        |
| mat        |
| mat        |
| mat        |
| mat        |
+------------+

SELECT LENGTH(SUBSTRING_INDEX(SUBSTRING_INDEX(@string,"|",i),"|",-1))x FROM ints ORDER BY x DESC LIMIT 1;
+------+
| x    |
+------+
|   10 |
+------+

Neither your answer or Mani Deep's answer allow for an arbitrary number of newlines per row, but your answer come nearer to the mark. You've given me enough to do what I wanted, however I think you're comment 'just because you can, doesn't mean you should' is correct in this case. — Dom, Apr 17 '15 at 14:40
Well you can just cross join the ints table as often as could be required, but yes, the point still stands. — Strawberry, Apr 17 '15 at 15:08

score 1 · Answer 2 · edited May 23 '17 at 10:33

check this sqlfiddle

CREATE TABLE tablename (
  id INT,
  name VARCHAR(50));

INSERT INTO tablename VALUES
  (1, 'the cat\nsat on the\nmat'),
  (2, 'd');

Query:

SELECT
max(length(SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.name, '\n', numbers.n), '\n', -1))) name
FROM
(SELECT 1 n UNION ALL SELECT 2
 UNION ALL SELECT 3 UNION ALL SELECT 4) numbers INNER JOIN tablename
   ON CHAR_LENGTH(tablename.name)
 -CHAR_LENGTH(REPLACE(tablename.name, '\n', ''))>=numbers.n-1
 ORDER BY
 id, n

Result:

name
 10

reference: here

MySQL query to count longest line in text field

2 Answers2