sql server new users: How can I, if possible, get the maximum length of the data in a column, if
the datatype is text?

I Tried: select max(len(comments)) from Table1

but am getting errors:
Server: Msg 409, Level 16, State 2, Line 1
The maximum aggregate operation cannot take a void type data type as an
argument.
Server: Msg 8116, Level 16, State 1, Line 1
Argument data type text is invalid for argument 1 of len function.

Use the datalength() function instead of the len() function. Something
like:

create table #foo
(
id int identity(1,1) primary key clustered,
bar text
);

insert into #foo (bar) values ('This is a line of text');
insert into #foo (bar) values ('blah blah');
insert into #foo (bar) values (null);
insert into #foo (bar) values ('...');

select * from #foo;
go

-- This one works
select max(*datalength*(coalesce(bar,''))) from #foo;
go

-- This one doesn't
select max(*len*(coalesce(bar,''))) from #foo;
go

drop table #foo;

Don't worry about the coalesce() in my example - it's just so that nulls
are handled properly. Of course, if you use a Unicode character set
(like /ntext/ instead of /text/ for example) you will get twice the
number returned because datalength() counts the number of _bytes_ (2 per
character) rather than the number of _characters_, but for single byte
character sets (/text/, /varchar/, /char/) they're the same thing.

--
*mike hodgson*
blog: http://sqlnerd.blogspot.com



[quoted text, click to view]