在 SQL Server 中检索 VARCHAR 列的最大长度
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9592287/
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
Retrieve the maximum length of a VARCHAR column in SQL Server
提问by Milo LaMar
I want to find the longest VARCHARin a specific column of a SQL Server table.
我想VARCHAR在 SQL Server 表的特定列中找到最长的。
Here's an example:
下面是一个例子：
ID = INT IDENTITY
DESC = VARCHAR(5000)
ID | Desc
---|-----
1 | a
2 | aaa
3 | aa
What's the SQL to return 3? Since the longest value is 3 characters?
返回 3 的 SQL 是什么？由于最长的值是 3 个字符？
回答by aweis
Use the built-in functions for length and max on the description column:
在描述列中使用长度和最大值的内置函数：
SELECT MAX(LEN(DESC)) FROM table_name;
Note that if your table is very large, there can be performance issues.
请注意，如果您的表非常大，则可能存在性能问题。
回答by Abhishek Goel
for mysqlits length not len
对于mysql，它的长度不是 len
SELECT MAX(LENGTH(Desc)) FROM table_name
回答by bevada
Watch out!! If there's spaces they will not be considered by the LEN method in T-SQL. Don't let this trick you and use
小心！！如果有空格，T-SQL 中的 LEN 方法将不会考虑它们。不要让这个欺骗你并使用
select max(datalength(Desc)) from table_name
回答by codeguruinboise
For Oracle, it is also LENGTH instead of LEN
对于 Oracle，也是 LENGTH 而不是 LEN
SELECT MAX(LENGTH(Desc)) FROM table_name
Also, DESC is a reserved word. Although many reserved words will still work for column names in many circumstances it is bad practice to do so, and can cause issues in some circumstances. They are reserved for a reason.
此外，DESC 是保留字。尽管许多保留字在许多情况下仍可用于列名，但这样做是不好的做法，并且在某些情况下可能会导致问题。他们被保留是有原因的。
If the word Desc was just being used as an example, it should be noted that not everyone will realize that, but many will realize that it is a reserved word for Descending. Personally, I started off by using this, and then trying to figure out where the column name went because all I had were reserved words. It didn't take long to figure it out, but keep that in mind when deciding on what to substitute for your actual column name.
如果仅将 Desc 一词用作示例，则应注意并非每个人都会意识到这一点，但许多人会意识到它是 Descending 的保留字。就我个人而言，我从使用它开始，然后试图找出列名的去向，因为我只有保留字。很快就搞清楚了，但是在决定用什么来代替您的实际列名时请记住这一点。
回答by shaijut
Gives the Max Count of record in table
给出表中记录的最大计数
select max(len(Description))from Table_Name
Gives Record Having Greater Count
给出具有更大计数的记录
select Description from Table_Name group by Description having max(len(Description)) >27
Hope helps someone.
希望能帮助某人。
回答by Milo LaMar
SELECT MAX(LEN(Desc)) as MaxLen FROM table
回答by MSTdev
For sql server (SSMS)
对于 sql server (SSMS)
select MAX(LEN(ColumnName)) from table_name
This will returns number of characters.
这将返回字符数。
select MAX(DATALENGTH(ColumnName)) from table_name
This will returns number of bytes used/required.
这将返回使用/需要的字节数。
IF some one use varchar then use DATALENGTH.More details
如果有人使用 varchar 然后使用DATALENGTH. 更多细节
回答by Alexandru-Codrin Panaite
SELECT TOP 1 column_name, LEN(column_name) AS Lenght FROM table_name ORDER BY LEN(column_name) DESC
回答by diyguy
Many times you want to identify the row that has that column with the longest length, especially if you are troubleshooting to find out why the length of a column on a row in a table is so much longer than any other row, for instance. This query will give you the option to list an identifier on the row in order to identify which row it is.
很多时候，您想确定具有最长长度的列的行，尤其是在您进行故障排除以找出为什么表中某行的列长度比任何其他行都长的原因时。此查询将为您提供在行上列出标识符的选项，以便识别它是哪一行。
select ID, [description], len([description]) as descriptionlength
FROM [database1].[dbo].[table1]
where len([description]) =
(select max(len([description]))
FROM [database1].[dbo].[table1]
