如何在表列数据中找到最长的字符串

小开

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.

This query gives all values with the maximum length. With a TOP 1 query you get only one of these, which is usually not desired.
This query must probably read the table twice: a full table scan to get the maximum length and another full table scan to get all values of that length. These operations, however, are very simple operations and hence rather fast. With a TOP 1 query a DBMS reads all records from the table and then sorts them. So the table is read only once, but a sort operation on a whole table is quite some task and can be very slow on large tables.
One would usually add 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.
If the string lengths have to be dealt with quite often, one might think of creating a computed column (calculated field) for it. This is available as of Ms Access 2010. But reading up on this shows that you cannot index calculated fields in MS Access. As long as this holds true, there is hardly any benefit from them. Applying LEN on the strings is usually not what makes such queries slow.

小开

最佳答案

The easiest way is:

select top 1 CR
from table t
order by len(CR) desc

Note that this will only return one value if there are multiple with the same longest length.

小开

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

小开

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)

小开

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

小开

For Oracle 11g:

SELECT COL1
FROM TABLE1
WHERE length(COL1) = (SELECT max(length(COL1)) FROM TABLE1);

小开

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

小开

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;

小开

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)

小开

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;

小开

If column datatype is text you should use DataLength function like:

select top 1 CR, DataLength(CR)
from tbl
order by DataLength(CR) desc

小开

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.

小开

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)

小开

 SELECT w.DEPARTMENT
FROM  Worker w
group by w.DEPARTMENT
order by length(w.DEPARTMENT) DESC
LIMIT 2 ;

小开

In MariaDB only length and char_length worked for me.

If you use non-english letters, you better use char_length. For example:

select
e.id,
home.name,
LENGTH(home.name) as length,
CHAR_LENGTH(home.name) as char_length
from events e
left join teams home on e.home_id  = home.id
order by CHAR_LENGTH(home.name) desc