Insert end space into VARCHAR SQL Server column

Question

I am trying to insert trailing spaces into a VARCHAR (50) column, and inserting SQL seems to disable them. Here is my code:

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

Unfortunately, this returns a length of zero, which means that `` was inserted as ''. I need empty space for this column - any ideas?

Answer 1

Use DATALENGTH , not LEN , because LEN doesn’t Spacing .

Take this string of zero length, 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 spaces.

Answer 3

It is also best to know that SQL Server follows ANSI / ISO SQL-92, filling in the character strings used in the comparison so that their match matches before they are compared. Thus, you can use the LIKE predicate to compare [1]

[one]
How SQL Server compares rows with trailing spaces http://support.microsoft.com/kb/316626