Re: RE: Get max length of data for a column

Evening all,

On 17/04/12 17:31, MCALLISTER, MICHAEL CTR AU Contractor AETC AETC/A3IS 
wrote:
> Select max(length(your_col_name)) as max_length
>> From your_table_name;

Not quite! The above only works for varchars. Char columns will always 
return the defined length and not the length of the data.


SQL> create table fred(c char(100), v varchar2(100));
Table created.

SQL> insert into fred(c,v) values ('one','one');
1 row created.

SQL> insert into fred(c,v)
   2  values ('four hundred and seven','four hundred and seven');
1 row created.

SQL> commit;
Commit complete.

SQL> select trim(c) as C, length(c) as len_c, length(v) as len_v
   2  from fred;

C				    LEN_C      LEN_V
------------------------------ ---------- ----------
one				      100	   3
four hundred and seven		      100	  22

CHAR columns always return the defined length for a call to length(). 
Varchar2 return the length of the data.

For CHAR columns you have to use the trim() function (or rtrim() if you 
wish to exclude only trailing spaces):


SQL> select rtrim(c) as c, length(rtrim(c)) as len_c, length(v) as len_v
   2  from fred;

C				    LEN_C      LEN_V
------------------------------ ---------- ----------
one					3	   3
four hundred and seven		       22	  22


-- 
Cheers,
Norm. [TeamT]


------------------------------------

Yahoo! Groups Links

<*> To visit your group on the web, go to:
    http://groups.yahoo.com/group/toad/

<*> Your email settings:
    Individual Email | Traditional

<*> To change settings online go to:
    http://groups.yahoo.com/group/toad/join
    (Yahoo! ID required)

<*> To change settings via email:
    [email protected] 
    [email protected]

<*> To unsubscribe from this group, send an email to:
    [email protected]

<*> Your use of Yahoo! Groups is subject to:
    http://docs.yahoo.com/info/terms/