21

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?

Leigh Riffel
  • 23,854
  • 16
  • 78
  • 152
AMH
  • 353
  • 1
  • 2
  • 7

2 Answers2

30

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
Oleg Dok
  • 3,387
  • 25
  • 29
-2

You can simply add a white space to your condition.

set @X= 'John           \n'

Trailing Spaces in String Functions

Martin Smith
  • 84,644
  • 15
  • 245
  • 333
Yiping
  • 227
  • 2
  • 8
  • 5
    \n has no significance in SQL Server. It is not interpreted as a new line. This is not an answer to the question asked anyway. – Martin Smith Dec 10 '16 at 19:05
  • @MartinSmith but it is written in MSDN "If you have to have trailing spaces in your string, you should consider appending a white space character at the end, so that SQL Server does not trim the string." – Yiping Dec 14 '16 at 05:07