SQL 如何在表列数据中找到最长的字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21885267/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to find longest string in the table column data
提问by vuyy1182
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 CRcolumn 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
我想从CR列中检索数据，其中它具有最长的文本字符串，即在当前表中它是;#WR_1;#WR_2;#WR_3;#WR_4;#。我正在使用
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
但我需要的不是长度（26），我想要这样
Max_Length_String
----------------------------------------
;#WR_1;#WR_2;#WR_3;#WR_4;#
回答by Gordon Linoff
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.
请注意，如果存在多个具有相同最长长度的值，则这只会返回一个值。
回答by Thorsten Kettner
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
DISTINCTto my query (SELECT DISTINCT CR FROM ...), so as to get every value just once. That wouldbe 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
LENon the strings is usually not what makes such queries slow.
- 此查询给出所有具有最大长度的值。使用 TOP 1 查询，您只能获得其中之一，这通常是不希望的。
- 此查询可能必须读取该表两次：一次全表扫描以获取最大长度，另一次全表扫描以获取该长度的所有值。然而，这些操作是非常简单的操作，因此速度相当快。通过 TOP 1 查询，DBMS 从表中读取所有记录，然后对它们进行排序。所以该表只读取一次，但对整个表的排序操作是相当多的任务，并且在大表上可能会非常慢。
- 通常会添加
DISTINCT到我的查询 (SELECT DISTINCT CR FROM ...) 中，以便只获取一次每个值。这将是一个排序操作，但仅限于已经找到的少数记录。再说一遍，没什么大不了的。 - 如果必须经常处理字符串长度，可能会考虑为它创建一个计算列（计算字段）。这在 Ms Access 2010 中可用。但阅读此内容表明您无法在 MS Access 中索引计算字段。只要这一点成立，它们就几乎没有任何好处。应用于
LEN字符串通常不是使此类查询变慢的原因。
回答by xeraphim
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
但我敢肯定，有一种更优雅的方式来做到这一点
回答by tmthyjames
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.
这是“postgres 中最长的字符串”谷歌搜索的第一个结果，所以我会把我的答案放在这里，供那些寻找 postgres 解决方案的人使用。
SELECT max(char_length(column)) AS Max_Length_String FROM table
SELECT max(char_length(column)) AS Max_Length_String FROM table
postgres docs: http://www.postgresql.org/docs/9.2/static/functions-string.html
postgres 文档：http: //www.postgresql.org/docs/9.2/static/functions-string.html
回答by kavehmb
For Oracle 11g:
对于 Oracle 11g：
SELECT COL1
FROM TABLE1
WHERE length(COL1) = (SELECT max(length(COL1)) FROM TABLE1);
回答by Srinivas Rathikrindi
For Postgres:
对于 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
这将为您提供字符串本身，针对@Thorsten Kettner 回答中的 postgres 进行修改
回答by Shobi
With two queries you can achieve this. This is for mysql
通过两个查询，您可以实现这一点。这是用于 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;
回答by pjammer
To answer your question, and get the Prefixtoo, for MySQL you can do:
要回答您的问题并获得Prefix同样的信息，对于 MySQL，您可以执行以下操作：
select Prefix, CR, length(CR) from table1 order by length(CR) DESC limit 1;
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)
回答by tHiNk_OuT_oF_bOx
Instead of SELECT max(len(CR)) AS Max_Length_String FROM table1
而不是 SELECT max(len(CR)) AS Max_Length_String FROM table1
Use
用
SELECT (CR) FROM table1
SELECT (CR) FROM table1
WHERE len(CR) = (SELECT max(len(CR)) FROM table1)
WHERE len(CR) = (SELECT max(len(CR)) FROM table1)
