SQL oracle求一列最长行的长度
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3361761/
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
Find the length of the longest row in a column in oracle
提问by electricsheep
Does anybody know if there is a way to find what the length of the longest row in a column in Oracle?
有谁知道是否有办法找到 Oracle 中列中最长行的长度？
Basically I need to get the length of the longest row and then use that length plus 1
with SUBSTRto make the output of the column one character longer than the longest string.
基本上我需要获得最长行的长度，然后使用该长度加 1SUBSTR使列的输出比最长的字符串长一个字符。
Thanks
谢谢
EDIT:
编辑：
Thanks for the advice.
感谢您的建议。
However, the MAX(LENGTH(column_name)) AS MAXLENGTHapproach gives me the number I want but when I try to use it with SUBSTR(column_name,1, MAXLENGTH)I get an invalid identifier error.
但是，该MAX(LENGTH(column_name)) AS MAXLENGTH方法为我提供了我想要的数字，但是当我尝试使用它时，出现SUBSTR(column_name,1, MAXLENGTH)了无效标识符错误。
SO I made a function to return the numberI wanted then used:
所以我做了一个函数来返回我想要的数字然后使用：
SUBSTR(column_name,1,maxlengthfunc)
This gave me the following output:
这给了我以下输出：
SUBSTR(NAME,1,MAXLENGTHFUNC)
Rather than:
而不是：
SUBSTR(NAME, 1, 19)
And it didn't shrink the output column size like I needed.
它没有像我需要的那样缩小输出列的大小。
Also
还
RTRIM(name)||' '
didn't do anything for me in SQL developer.
在 SQL 开发人员中没有为我做任何事情。
Thanks.
谢谢。
回答by APC
This will work with VARCHAR2 columns.
这将适用于 VARCHAR2 列。
select max(length(your_col))
from your_table
/
CHAR columns are obviously all the same length. If the column is a CLOB you will need to use DBMS_LOB.GETLENGTH(). If it's a LONG it's really tricky.
CHAR 列的长度显然都相同。如果该列是 CLOB，您将需要使用 DBMS_LOB.GETLENGTH()。如果是 LONG，那真的很棘手。
回答by sealz
SELECT max(length(col_name)+1) as MyOutput
FROM table_Name
Normal output would look like
正常输出看起来像
MyOutput
1 5
New output would look like
新输出看起来像
MyOutput
1 6
回答by Colin Pickard
This should do what you want:
这应该做你想做的：
select max(length(MyColumn)) from MyTable;
Depending on what you are trying to achieve, you may also be insterested to know that you can output the data in the column plus exactly one space like this:
根据您要实现的目标，您可能还想知道您可以输出列中的数据加上一个空格，如下所示：
select rtrim(MyColumn)||' ' from MyTable;
回答by RedFilter
select max(length(MyColumn)) as MaxLength
from MyTable
回答by PaulJWilliams
select max(LENGTH(column_name)) from table_name.
从 table_name 中选择 max(LENGTH(column_name))。
回答by ThinkJet
w/o function:
不带功能：
select
rpad(tbl.column_name, length_info.max_length+1, ' ') as target_string
from
table_name tbl,
(
select max(length(column_name)) max_length
from my_table
)
length_info
with your function:
与您的功能：
select
rpad(tbl.column_name, MaxLengthFunc + 1, ' ') as target_string
from
my_table tbl
declare your function as determinicticfor better performance:
声明您的函数以determinictic获得更好的性能：
create or replace function MaxLengthFunc
return number
deterministic
as
vMaxLen number;
begin
select max(length(column_name))
into vMaxLen
from table_name;
return vMaxLen;
end;
回答by mrrooster
To pad all values in a column to the longest value +1 you can do:
要将列中的所有值填充为最长值 +1，您可以执行以下操作：
SELECT RPAD( column_name ,(SELECT MAX(LENGTH( column_name ))+1 FROM table)) FROM table;
回答by AndyDaSilva52
For use the max in column definition, i suggest the right approach:
为了在列定义中使用最大值，我建议使用正确的方法：
create or replace FUNCTION F_GET_MAX_LENGTH_TAB_COLUMN
(
pCOLUMN_NAME IN VARCHAR2
, pTABLE_NAME IN VARCHAR2
, pOWNER IN VARCHAR2
) RETURN NUMBER AS
vLength NUMBER;
BEGIN
BEGIN
SELECT DATA_LENGTH
INTO vLength
FROM ALL_TAB_COLUMNS
WHERE
COLUMN_NAME = pCOLUMN_NAME
AND TABLE_NAME = pTABLE_NAME
AND OWNER = pOWNER
;
EXCEPTION
WHEN NO_DATA_FOUND THEN
vLength := 0;
END;
RETURN vLength;
END F_GET_MAX_LENGTH_TAB_COLUMN;
Just call the function:
只需调用函数：
SELECT F_GET_MAX_LENGTH_TAB_COLUMN(
pCOLUMN_NAME => 'AGN_ST_NOME',
pTABLE_NAME => 'GLO_AGENTES',
pOWNER => 'MGGLO' )
FROM DUAL;
回答by hol
To make the maxlength useable you may want to get it from a imbedded select
为了使 maxlength 可用，您可能希望从嵌入式选择中获取它
select <do something with maxlength here>
from
(select x.*,
( select max(length(yourcolumn)) from yourtable) as maxlength
from yourtable x)
