Find the length of the longest row in a column in oracle

sql oracle plsql oracle-sqldeveloper
electricsheep picture electricsheep · Jul 29, 2010 · Viewed 93.4k times · Source

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 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)

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.

Answer

APC picture APC · Jul 29, 2010

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.

Related questions

Rudimentary issue: basic PL/SQL console output?

I am using SQL Developer and want to output the contents of a variable to the console using DBMS_OUTPUT.PUT_LINE(). I am running the following code that adds the numbers 1 through 5 inclusive but I'm not seeing any output. …

Read More
Using SELECT inside COALESCE

How do I correct the following SQL code, specifically the COALESCE part? insert into Stmt G (ID,blah,foo) select coalesce(SELECT ID FROM Stmt G WHERE G.CLAIMNO=C.CLNUMBER, select StmtSeq.nextval from dual), c.blah, d.foo …

Read More
SQL error "ORA-01722: invalid number"

A very easy one for someone, The following insert is giving me the ORA-01722: invalid number why? INSERT INTO CUSTOMER VALUES (1,'MALADY','Claire','27 Smith St Caulfield','0419 853 694'); INSERT INTO CUSTOMER VALUES (2,'GIBSON','Jake','27 Smith St Caulfield','0415 713 598…

Read More