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

Norm

Thanks for that clarification. To be honest, we don't really use CHAR here
anymore, which is why I've either forgotten that important piece of
information, or (worse) never knew it. Out of interest, is there any reason
why one would want to use CHAR rather than VARCHAR?

Regards,

Mike McAllister 
Principal Systems Engineer
Decypher
DSN:                      487-3751
Commercial:       (210) 652-3751
Cell:                       (512) 423-7447
Email:                    [email protected]


-----Original Message-----
From: [email protected] [mailto:[email protected]] On Behalf Of Norman
Dunbar
Sent: Tuesday, April 17, 2012 1:01 PM
To: [email protected]
Subject: Re: [toad] 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]