Find the length of the longest row in a column in oracle
Solution 1
This will work with VARCHAR2 columns.
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.
Solution 2
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
Solution 3
select max(length(MyColumn)) as MaxLength
from MyTable
Solution 4
select max(LENGTH(column_name)) from table_name.
Solution 5
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;
electricsheep
Updated on July 31, 2020Comments
-
electricsheep almost 4 years
Does anybody know if there is a way to find what the length of the longest row in a column in Oracle?
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.Thanks
EDIT:
Thanks for the advice.
However, the
MAX(LENGTH(column_name)) AS MAXLENGTH
approach gives me the number I want but when I try to use it withSUBSTR(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)
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.
Thanks.