Max character count for a column

[Dabase]

Hi,

Is there any way that I can obtain the maximum character count (including spaces) for a column?

Thanks
Dabase

 

[vongrunt]

Max defined column size: see information_schema.columns.character_maximum_length

Max. used size: select max(len(somecolumn)) from sometable

?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[gmmastros]

Len will work unless your column is defined at Date_Type = Text. The len function does not work on text columns. In that case, you'll need to use datalength.

select max(datalength(somecolumn)) from sometable

For Char, nChar, VarChar, and nVarChar use Len
For Text and nText, use DataLength




-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[druer]

I would use DataLength for all of the character datatype columns as well, as LEN will not return the correct length for fields that have trailing spaces, whereas DATALENGTH will and it will work for char,varchar fields as well as Text.

 

[gmmastros]

Druer,

DataLength does not work well with Char and nChar columns. It returns the defined size instead of the actual size for the data. Check it out....

create table #Temp(Name_Char Char(20), Name_VarChar VarChar(20), Name_Text Text)

Insert Into #Temp Values('George', 'George', 'George')

Select Name_Char,
       len(Name_Char) As Length_Char, 
       DataLength(Name_Char) As DataLength_Char,
       len(Name_VarChar) As Len_VarChar, 
       DataLength(Name_VarChar) As DataLength_VarChar,
       DataLength(Name_Text) As DataLength_Text 
From   #Temp

drop table #Temp

The DataLength for the Char column returns 20 (not 6 as expected).

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[vongrunt]

Also: DATALENGTH() returns storage size - for nvarchar/ntext this equals twice "logical size".

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[druer]

We are obviously seeing different results then. I'm on SQL 2000 (sp 2) (so shoot me vendor of application database can't work on (sp 3). However:

create table #mytemp (field1 varchar(32))
insert into #mytemp values ('value ')
select field1, len(field1), datalength(field1) from #mytemp

yeilds
5 for the LEN because of the 5 trailing spaces, but Datalength returns 10 (not 32) which includes the trailing spaces.

Books on line defines LEN function:
Returns the number of characters, instead of the number of bytes, of the specified string expression, excluding trailing blanks

Def for DATALENGTH is:
Returns the number of bytes used to represent any expression. Syntax DATALENGTH ( expression ) Arguments expression Is an expression of any type.