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

user_333943 0
Viewed 110712

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.

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 work when run.

oracle-sqldeveloper plsql oracle sql
9 Answers

To pad all values in a column to the longest value +1 you can do:

SELECT RPAD( column_name ,(SELECT MAX(LENGTH( column_name ))+1 FROM table)) FROM table;
user_98286780

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;
user_20578540
Related