CODEWITHSUNDEEP
sqloracle-databaseplsqloracle-sqldeveloper
electricsheep
electricsheep

Reputation: 5224

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

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.

Upvotes: 21

Views: 129895

Answers (9)

AndyDaSilva52
AndyDaSilva52

Reputation: 159

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;

Upvotes: 0

mrrooster
mrrooster

Reputation: 1

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;

Upvotes: 0

sealz
sealz

Reputation: 5408

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

Upvotes: 8

ThinkJet
ThinkJet

Reputation: 6735

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 determinictic for better performance:

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;

Upvotes: 1

hol
hol

Reputation: 8423

To make the maxlength useable you may want to get it from a imbedded select

select <do something with maxlength here> 
from
(select x.*, 
( select max(length(yourcolumn)) from yourtable) as maxlength 
from yourtable x)

Upvotes: 0

pauljwilliams
pauljwilliams

Reputation: 19225

select max(LENGTH(column_name)) from table_name.

Upvotes: 3

APC
APC

Reputation: 146229

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.

Upvotes: 40

Colin Pickard
Colin Pickard

Reputation: 46643

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;

Upvotes: 3

D&#39;Arcy Rittich
D&#39;Arcy Rittich

Reputation: 171411

select max(length(MyColumn)) as MaxLength
from MyTable

Upvotes: 3

Related Questions