Setting a field

[mdav2]

I have a table with a field that is a varchar(1) and allows null values. If I try and update this to a blank space with syntax

update table set field=' '

This doesn't set the value to a single space. I checked this with a select statement using the LEN function and that returns the length as 0.

I've looked at trying to use ASCII and CHAR commands to set these but neither seem to work. Does SQL Server just not like single spaces in field names?

If you are wondering why I am doing this it's because we have had a third party applciation developed that checks for a single space in the field.

Mark Davies
Warwickshire County Council

 

[bborissov]

No, that is because it is a VARCHAR() field.
Varchar field removes all trailing spaces from the data.
Make that field to be CHAR(1) instead of varchar(1).

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[gmmastros]

I checked this with a select statement using the LEN function

The LEN function ignores trailing spaces. Try using the DataLength function instead.

DataLength is similar to Len with a couple of differences. DataLength does not ignore trailing spaces, and DataLength returns the storage requirements instead of character length. With varchar and char, it's the same thing (one byte per character). With nvarchar/nchar, it takes 2 bytes to store a single character, so datalength returns 2 bytes for each characters.

To show you what I mean...

Select Len(' ') As Len_Of_Space,
       DataLength(' ') As DataLength_Of_Space,
       Datalength(N' ') As DataLength_Of_Unicode_Space

The result is 0,1,2.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

varchar(1) field makes 0 sense. For all fields that are less than 5 characters it would make more sense to use char type rather than varchar as varchar has some overhead.

PluralSight Learning Library

 

[mdav2]

Thanks for the responses, the third party app seems to work OK with blank fields so this is not causing me a problem. I just ran the SQL create tables I got from the developer and being new to SQL Server so didn't realise that a VARCHAR field would do this. I wouldn't want to put single spaces into fields if I were developing something myself because they're difficult to see anyway. Much easier to use some kind of visual marker.

Mark Davies
Warwickshire County Council

 

[markros]

varchar(1) doesn't make sense since varchar has an overhead. For fields less than 5 chars, CHAR type is more appropriate.

PluralSight Learning Library