Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

When I use a Varchar with spaces it ignores the spaces at the end.


declare @X varchar(50)


set  @X= 'John' the same as...

set @X= 'John           '

It considers these to be equal. How can I cause the system to recognize these as different?

share|improve this question

migrated from Jan 11 '12 at 13:18

This question came from our site for professional and enthusiast programmers.

up vote 10 down vote accepted

Everything is just according the ANSI standard:

Trailing blanks explained:

SQL Server follows the ANSI/ISO SQL-92 specification (Section 8.2, , General rules #3) on how to compare strings with spaces. The ANSI standard requires padding for the character strings used in comparisons so that their lengths match before comparing them. The padding directly affects the semantics of WHERE and HAVING clause predicates and other Transact-SQL string comparisons. For example, Transact-SQL considers the strings 'abc' and 'abc ' to be equivalent for most comparison operations.

The only exception to this rule is the LIKE predicate. When the right side of a LIKE predicate expression features a value with a trailing space, SQL Server does not pad the two values to the same length before the comparison occurs. Because the purpose of the LIKE predicate, by definition, is to facilitate pattern searches rather than simple string equality tests, this does not violate the section of the ANSI SQL-92 specification mentioned earlier.

Here's a well known example of all the cases mentioned above:

DECLARE @b varchar(10)

SET @a = '1'
SET @b = '1 ' --with trailing blank

    @a = @b 
AND @b LIKE @a

Here's some more detail about trailing blanks and the LIKE clause.

BUT if you want to differ them - you may decide to use DATALENGTH function instead of LEN, because

SELECT 1 WHERE LEN('John ') = LEN('John')

will put you 1 instead of


The solution is

  • to use DATALENGTH function to differ between strings
  • to cast the string into NVARCHAR type - may be better declare this type to parameter of SP
share|improve this answer
so what's the solution – AMH Jan 11 '12 at 9:52
Explained at the end – Oleg Dok Jan 11 '12 at 9:58
I told you that LEN will be the same, DATALENGTH - NOT! How to find leftmost space - run SELECT CHARINDEX(N' ', N'John ') – Oleg Dok Jan 11 '12 at 10:23
@onedaywhen: I don't think datalength() is a standard ANSI function. It's a Microsoft only thing - at least no other DBMS implements it. – a_horse_with_no_name Jan 11 '12 at 14:06
SQL2003 specifies OCTET_LENGTH, but all common platforms differ from this. SQL Server does support it via an ODBC scalar function: SELECT {FN OCTET_LENGTH('ABC ')} is valid. – Paul White Jan 11 '12 at 14:32

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.