CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008string
SqlRyan
SqlRyan

Reputation: 33944

Insert a trailing space into a SQL Server VARCHAR column

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?

Upvotes: 7

Views: 6862

Answers (3)

Gennady Vanin Геннадий Ванин
Gennady Vanin Геннадий Ванин

Reputation: 10424

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

Upvotes: 1

OMG Ponies
OMG Ponies

Reputation: 332771

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

Upvotes: 11

SteveCav
SteveCav

Reputation: 6729

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

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

Upvotes: 4

Related Questions