Take the 2-minute tour ×
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. It's 100% free, no registration required.

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

ex:

declare @X varchar(50)

This...

set  @X= 'John'

...is 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
add comment

migrated from stackoverflow.com Jan 11 '12 at 13:18

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

1 Answer

up vote 6 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 @a VARCHAR(10)
DECLARE @b varchar(10)

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

SELECT 1
WHERE 
    @a = @b 
AND @a NOT LIKE @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

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

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
    
the len is the same , another question how to get the first space on the left –  AMH Jan 11 '12 at 10:15
    
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
1  
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
show 2 more comments

Your Answer

 
discard

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.