Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How can I make SQL Server return FALSE for comparing varchars with and without trailing spaces?

If I deliberately store trailing spaces in a VARCHAR column, how can I force SQL Server to see the data as mismatch?

SELECT 'foo' WHERE 'bar' = 'bar    '

I have tried:

SELECT 'foo' WHERE LEN('bar') = LEN('bar    ')

One method I've seen floated is to append a specific character to the end of every string then strip it back out for my presentation... but this seems pretty silly.

Is there a method I've overlooked?

I've noticed that it does not apply to leading spaces so perhaps I run a function which inverts the character order before the compare.... problem is that this makes the query unSARGable....

like image 437
Matthew Avatar asked Oct 15 '10 00:10

Matthew


People also ask

How do I get rid of trailing spaces in SQL?

SQL Server TRIM() Function The TRIM() function removes the space character OR other specified characters from the start or end of a string. By default, the TRIM() function removes leading and trailing spaces from a string.

How SQL Server compare strings with trailing spaces?

Also, from the support page on How SQL Server Compares Strings with Trailing Spaces: SQL Server follows the ANSI/ISO SQL-92 specification 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.

Does SQL Server ignore trailing spaces?

Takeaway: According to SQL Server, an identifier with trailing spaces is considered equivalent to the same identifier with those spaces removed.


1 Answers

From the docs on LEN (Transact-SQL):

Returns the number of characters of the specified string expression, excluding trailing blanks. To return the number of bytes used to represent an expression, use the DATALENGTH function

Also, from the support page on How SQL Server Compares Strings with Trailing Spaces:

SQL Server follows the ANSI/ISO SQL-92 specification 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.

Update: I deleted my code using LIKE (which does not pad spaces during comparison) and DATALENGTH() since they are not foolproof for comparing strings

This has also been asked in a lot of other places as well for other solutions:

  • SQL Server 2008 Empty String vs. Space
  • Is it good practice to trim whitespace (leading and trailing)
  • Why would SqlServer select statement select rows which match and rows which match and have trailing spaces
like image 126