find longest string in column

Discussion:

Mertens Bram

2002-11-06 12:29:58 UTC

Hi,

How can I find the longest string in a column?

I can probably write a PHP-script that checks the str_length
recursively, but I would like to be able to do this within MySQL...

I would like to have the value and the length of the string if this is
possible. Is this possible or should I try to write the PHP-script?

I couldn't find anything relevant in the documentation or the archives,
but I hope I just missed it...

TIA,

Bram
[sql, query]
--
# Mertens Bram "M8ram" <bram-***@linux.be> Linux User #249103 #
# Red Hat Linux 7.3 KDE 3.0.0-10 kernel 2.4.18-3 i686 128MB RAM #

---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail <mysql-***@lists.mysql.com>
To unsubscribe, e-mail <mysql-unsubscribe-gcdmg-mysql=***@lists.mysql.com>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Joseph Bueno

2002-11-06 13:43:57 UTC

Permalink

Post by Mertens Bram
Hi,
How can I find the longest string in a column?
I can probably write a PHP-script that checks the str_length
recursively, but I would like to be able to do this within MySQL...
I would like to have the value and the length of the string if this is
possible. Is this possible or should I try to write the PHP-script?
I couldn't find anything relevant in the documentation or the archives,
but I hope I just missed it...
TIA,
Bram
[sql, query]

Hi,

You could try:

SELECT string,length(string) as len
FROM mytable
ORDER BY len DESC
LIMIT 1

regards,
Joseph Bueno

---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail <mysql-***@lists.mysql.com>
To unsubscribe, e-mail <mysql-unsubscribe-gcdmg-mysql=***@lists.mysql.com>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Mertens Bram

2002-11-06 14:12:44 UTC

Permalink

SELECT MAX(LENGTH(column)) FROM db

Thx Brent! This works perfectly!

That will give you the length of the longest string but won't tell you
what the value is. To get the value also, you could do something like
SELECT MAX(LENGTH(column)) AS length,column FROM db GROUP BY column
ORDER BY length LIMIT 1

I had to edit this little bit because it returned the shortest string...
Here's what works for me:

SELECT MAX(LENGTH(column_name)) AS length,column_name FROM table_name
GROUP BY column_name ORDER BY length DESC LIMIT 1;

So the 'db' in your suggestion has been replaced by the table_name and I
added the 'DESC' to get the longest string!

Thx again!

That query could get very slow on large databases since it can't use an
index and traverses the entire database.

Luckily the db is not that big at the moment ( only 3157 records)...

Post by Mertens Bram
How can I find the longest string in a column?
I would like to have the value and the length of the string if this is
possible. Is this possible or should I try to write the PHP-script?

--
# Mertens Bram "M8ram" <bram-***@linux.be> Linux User #249103 #
# Red Hat Linux 7.3 KDE 3.0.0-10 kernel 2.4.18-3 i686 128MB RAM #

---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail <mysql-***@lists.mysql.com>
To unsubscribe, e-mail <mysql-unsubscribe-gcdmg-mysql=***@lists.mysql.com>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Mertens Bram

2002-11-06 16:34:24 UTC

Permalink

sql,query
Hi,
SELECT column_name,length(column_name) as len FROM table_name
ORDER BY len DESC
LIMIT 1
+------+------+
| text | len |
+------+------+
| NULL | NULL |
+------+------+
1 row in set (0.43 sec)
(Where text is the column_name)
On columns that don't contain NULL values it works fine, so I edited it
SELECT column_name,length(column_name) as len FROM table_name
WHERE column_name IS NOT NULL
ORDER BY len DESC
LIMIT 1
Which works even on the first column...
Is it normal behaviour that NULL values are considered to be "longer"
strings than non-NULL values?
Regards