Insert a trailing space into a SQL Server VARCHAR column

Question

I'm trying to insert trailing spaces into a VARCHAR(50) column and the SQL insert seems to be cutting them off. Here's my code:

create table #temp (field varchar(10));
insert into #temp select ' ';
select LEN(field) from #temp;

Unfortunately, this returns a length of zero, meaning the ' ' was inserted as a ''. I need a blank space to be inserted for this column - any ideas?

Answer 1

Use DATALENGTH, not LEN, because LEN doesn't process spaces.

Take this zero length string, for example:

SELECT LEN(' ') AS len, 
       DATALENGTH(' ') AS datalength

Results:

len   datalength
-----------------
0     1

Answer 2

From http://msdn.microsoft.com/en-us/library/ms190329.aspx:

LEN Returns the number of characters of the specified string expression, excluding trailing blanks.

Answer 3

You better be aware also that SQL Server follows ANSI/ISO SQL-92 padding the character strings used in comparisons so that their lengths match before comparing them. So, you may want to use LIKE predicate for comparisons [1]

[1]
How SQL Server Compares Strings with Trailing Spaces
http://support.microsoft.com/kb/316626