在oracle中找到一列中最长行的长度 [英] Find the length of the longest row in a column in oracle

问题描述

有人知道是否有一种方法可以找到最长的长度 在Oracle的列中排成一行?

Does anybody know if there is a way to find what the length of the longest row in a column in Oracle?

基本上，我需要获取最长行的长度，然后使用该长度加1 使用SUBSTR使列的输出比最长的字符串长一个字符.

Basically I need to get the length of the longest row and then use that length plus 1 with SUBSTR to make the output of the column one character longer than the longest string.

谢谢

感谢您的建议.

但是，MAX(LENGTH(column_name)) AS MAXLENGTH方法会给我我想要的号码，但是当我尝试将其与SUBSTR(column_name,1, MAXLENGTH)一起使用时，我会得到一个无效的标识符错误.

However, the MAX(LENGTH(column_name)) AS MAXLENGTH approach 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.

所以我做了一个函数来返回我想要然后使用的数字:

SO I made a function to return the numberI wanted then used:

SUBSTR(column_name,1,maxlengthfunc)

这给了我以下输出:

SUBSTR(NAME,1,MAXLENGTHFUNC)

而不是:

SUBSTR(NAME, 1, 19)

它并没有像我所需要的那样缩小输出列的大小.

And it didn't shrink the output column size like I needed.

也

RTRIM(name)||' '

在SQL开发人员中对我没有任何帮助.

didn't do anything for me in SQL developer.

谢谢.

推荐答案

这将适用于VARCHAR2列.

This will work with VARCHAR2 columns.

select max(length(your_col))
from your_table
/

CHAR列的长度显然都相同.如果该列是CLOB，则需要使用DBMS_LOB.GETLENGTH().如果它很长，那真的很棘手.

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.

这篇关于在oracle中找到一列中最长行的长度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！