I've a table contains the columns like
Prefix | CR
----------------------------------------
g | ;#WR_1;#WR_2;#WR_3;#WR_4;#
v | ;#WR_3;#WR_4;#
j | WR_2
m | WR_1
d | ;#WR_3;#WR_4;#
f9 | WR_3
I want to retrieve data from CR column WHERE it has the longest text string i.e in current table it is ;#WR_1;#WR_2;#WR_3;#WR_4;#. I'm using
SELECT max(len(CR)) AS Max_Length_String FROM table1
But it retuns
Max_Length_String
----------------------------------------
26
But what i need is not the length (26), i wanted like this
Max_Length_String
----------------------------------------
;#WR_1;#WR_2;#WR_3;#WR_4;#
Instead of SELECT max(len(CR)) AS Max_Length_String FROM table1
Use
SELECT (CR) FROM table1
WHERE len(CR) = (SELECT max(len(CR)) FROM table1)
To answer your question, and get the Prefix
too, for MySQL you can do:
select Prefix, CR, length(CR) from table1 order by length(CR) DESC limit 1;
and it will return
+-------+----------------------------+--------------------+
| Prefix| CR | length(CR) |
+-------+----------------------------+--------------------+
| g | ;#WR_1;#WR_2;#WR_3;#WR_4;# | 26 |
+-------+----------------------------+--------------------+
1 row in set (0.01 sec)
If column datatype is text you should use DataLength function like:
select top 1 CR, DataLength(CR)
from tbl
order by DataLength(CR) desc
SELECT CITY,LENGTH(CITY) FROM STATION GROUP BY CITY ORDER BY LENGTH(CITY) ASC LIMIT 1;
SELECT CITY,LENGTH(CITY) FROM STATION GROUP BY CITY ORDER BY LENGTH(CITY) DESC LIMIT 1;
For Oracle 11g:
SELECT COL1
FROM TABLE1
WHERE length(COL1) = (SELECT max(length(COL1)) FROM TABLE1);
You can:
SELECT CR
FROM table1
WHERE len(CR) = (SELECT max(len(CR)) FROM table1)
Having just recieved an upvote more than a year after posting this, I'd like to add some information.
DISTINCT
to my query (SELECT DISTINCT CR FROM ...
), so as to get every value just once. That would be a sort operation, but only on the few records already found. Again, no big deal.LEN
on the strings is usually not what makes such queries slow.you have to do some changes by applying group by or query with in query.
"SELECT CITY,LENGTH(CITY) FROM STATION ORDER BY LENGTH(CITY) DESC LIMIT 1;"
it will return longest cityname from city.
For Postgres:
SELECT column
FROM table
WHERE char_length(column) = (SELECT max(char_length(column)) FROM table )
This will give you the string itself,modified for postgres from @Thorsten Kettner answer
In MySQL you can use,
(SELECT CITY,
LENGTH(CITY) AS CHR_LEN
FROM STATION
ORDER BY CHR_LEN ASC,
CITY
LIMIT 1)
UNION
(SELECT CITY,
LENGTH(CITY) AS CHR_LEN
FROM STATION
ORDER BY CHR_LEN DESC,
CITY
LIMIT 1)
This was the first result on "longest string in postgres" google search so I'll put my answer here for those looking for a postgres solution.
SELECT max(char_length(column)) AS Max_Length_String FROM table
postgres docs: http://www.postgresql.org/docs/9.2/static/functions-string.html
With two queries you can achieve this. This is for mysql
//will select shortest length coulmn and display its length.
// only 1 row will be selected, because we limit it by 1
SELECT column, length(column) FROM table order by length(column) asc limit 1;
//will select shortest length coulmn and display its length.
SELECT CITY, length(city) FROM STATION order by length(city) desc limit 1;
You can get it like this:
SELECT TOP 1 CR
FROM tbl
ORDER BY len(CR) DESC
but i'm sure, there is a more elegant way to do it
Source: Stackoverflow.com