SQL Server 2000 - Type char

Hi there

In my table I've got a column called membernote, type char with lenght 100.
If by example one of the fields has the word "example" and if I ask the lenght (LEN(membernote)) of that particular field it gives me 100 instead of seven.
I use this code with a mysql database and works perfect but it doens't with SQL2000 databse. Can anyone tell the soluction for this please?

Thanks

I think that datatype "char" stores extra space to complete the full length of the field, ie., u enter 7 character word in 100 length, so it adds 93 spaces to make it 100.

Thats why i use datatype "varchar", it'll store only 7 character in this case.

Have you tried trimming, like?
LEN(trim(membernote))

Sql Server 2000 doesn't have a TRIM() function. You can use RTRIM() to remove space from the right. LTRIM(RTRIM()) would work like a TRIM().

However, I wonder if you do this to a "char" datatype, will it actually change anything? I don't have a sql server in front of me right now, but it may be that you will need to use CAST or CONVERT to varchar or it may just keep putting the spaces back in.

So try LEN(RTRIM(CONVERT(varchar(100),membernote))) if Mickey's suggestion doesn't work.