Find the Length of the Longest Row in a Column in Oracle

How to select max length column data in oracle

Use a subquery to identify the max length:

SELECT code, name
FROM my_mst
WHERE LENGTH(name) = (SELECT MAX(LENGTH(name)) FROM my_mst)

This would work well with the particular sample data set you showed us. However, it has a limitation, namely that it would return multiple records if there were a tie for the max name length. If you wanted to break that tie using, e.g., the code, then you could consider using analytic functions:

SELECT t.*
FROM
(
    SELECT code, name,
        ROW_NUMBER() OVER (ORDER BY LENGTH(name) DESC, code) rn
    FROM my_mst
) t
WHERE t.rn = 1;

How to find longest string in the table column data

The easiest way is:

select top 1 CR
from table t
order by len(CR) desc

Note that this will only return one value if there are multiple with the same longest length.

Get max(length(column)) for all columns in an Oracle table

Got the below query to work -

DECLARE
  max_length INTEGER; --Declare a variable to store max length in.
  v_owner VARCHAR2(255) :='exampleSchema';     -- Type the owner of the tables you are looking at

BEGIN
  -- loop through column names in all_tab_columns for a given table
  FOR t IN (SELECT table_name, column_name FROM all_tab_cols where owner=v_owner and table_name = 'exampleTableName') LOOP

    EXECUTE IMMEDIATE 
    -- store maximum length of each looped column in max_length variable
    'select nvl(max(length('||t.column_name||')),0) FROM '||t.table_name
    INTO max_length;

    IF max_length >= 0 THEN -- this isn't really necessary but just to ignore empty columns. nvl might work as well
      dbms_output.put_line( t.table_name ||' '||t.column_name||' '||max_length ); --print the tableName, columnName and max length
    END IF;

  END LOOP;
END;

Do let me know if the comments explain it sufficiently, else I'll try to do better. Removing table_name = 'exampleTableName' might loop for all tables as well, but this is okay for me right now.

Get column value length, not column max length of value

LENGTH() does return the string length (just verified). I suppose that your data is padded with blanks - try

SELECT typ, LENGTH(TRIM(t1.typ))
FROM AUTA_VIEW t1;

instead.

As OraNob mentioned, another cause could be that CHAR is used in which case LENGTH() would also return the column width, not the string length. However, the TRIM() approach also works in this case.

Find the largest value from column using SQL?

Assuming that both columns are strings, and that column B uses single space for separators and no leading/trailing spaces, you can use this approach:

SELECT A, B
FROM MyTable
ORDER BY DESC LENGTH(B)-LENGTH(REPLACE(B, ' ', ''))
FETCH FIRST 1 ROW ONLY

The heart of this solution is LENGTH(B)-LENGTH(REPLACE(B, ' ', '')) expression, which counts the number of spaces in the string B.

Note: FETCH FIRST N ROWS ONLY is Oracle-12c syntax. For earlier versions use ROWNUM approach described in this answer.

---

Related Topics