Clash Royale CLAN TAG#URR8PPP

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.

SUBSTR

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.

MAX(LENGTH(column_name)) AS MAXLENGTH

SUBSTR(column_name,1, MAXLENGTH)

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.

9 Answers
9

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.

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;


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


select max(LENGTH(column_name)) from table_name.

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


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:

determinictic

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;


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)


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;


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;


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.