SHARE
TWEET

Comparing strings in SQL with trailing spaces

a guest Sep 29th, 2018 154 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -- Testing for this issue:
  2. -- https://improvingsoftware.com/2009/09/09/beware-of-this-trap-when-comparing-strings-in-t-sql-with-trailing-spaces/
  3. -- https://www.brentozar.com/archive/2017/04/surprising-behavior-trailing-spaces/
  4.  
  5. DECLARE @Value1 NVARCHAR(100), @Value2 NVARCHAR(100), @RESULT NVARCHAR(100)
  6.  
  7. SET @Value1 = 'apple'
  8. SET @Value2 = 'juice'
  9. SET @RESULT = ''
  10. IF ((@Value1 + 'EndOfString' = @Value2 + 'EndOfString' COLLATE SQL_Latin1_General_CP1_CS_AS) OR (ISNULL(@Value1, @Value2) IS NULL))
  11. SET @RESULT = 'Equal' ELSE SET @RESULT = 'Not Equal'
  12. PRINT 'Test 1: NVARCHAR. Values differ. Expected: Not Equal. Actual: ' + @RESULT
  13.  
  14. SET @Value1 = 'apple'
  15. SET @Value2 = 'apple'
  16. SET @RESULT = ''
  17. IF ((@Value1 + 'EndOfString' = @Value2 + 'EndOfString' COLLATE SQL_Latin1_General_CP1_CS_AS) OR (ISNULL(@Value1, @Value2) IS NULL))
  18. SET @RESULT = 'Equal' ELSE SET @RESULT = 'Not Equal'
  19. PRINT 'Test 2: NVARCHAR. Values identical. Expected: Equal. Actual: ' + @RESULT
  20.  
  21. SET @Value1 = NULL
  22. SET @Value2 = 'apple'
  23. SET @RESULT = ''
  24. IF ((@Value1 + 'EndOfString' = @Value2 + 'EndOfString' COLLATE SQL_Latin1_General_CP1_CS_AS) OR (ISNULL(@Value1, @Value2) IS NULL))
  25. SET @RESULT = 'Equal' ELSE SET @RESULT = 'Not Equal'
  26. PRINT 'Test 3: NVARCHAR. Left side null. Expected: Not Equal. Actual: ' + @RESULT
  27.  
  28. SET @Value1 = 'apple'
  29. SET @Value2 = NULL
  30. SET @RESULT = ''
  31. IF ((@Value1 + 'EndOfString' = @Value2 + 'EndOfString' COLLATE SQL_Latin1_General_CP1_CS_AS) OR (ISNULL(@Value1, @Value2) IS NULL))
  32. SET @RESULT = 'Equal' ELSE SET @RESULT = 'Not Equal'
  33. PRINT 'Test 4: NVARCHAR. Right side null. Expected: Not Equal. Actual: ' + @RESULT
  34.  
  35. SET @Value1 = NULL
  36. SET @Value2 = NULL
  37. SET @RESULT = ''
  38. IF ((@Value1 + 'EndOfString' = @Value2 + 'EndOfString' COLLATE SQL_Latin1_General_CP1_CS_AS) OR (ISNULL(@Value1, @Value2) IS NULL))
  39. SET @RESULT = 'Equal' ELSE SET @RESULT = 'Not Equal'
  40. PRINT 'Test 5: NVARCHAR. Both sides null. Expected: Equal. Actual: ' + @RESULT
  41.  
  42. SET @Value1 = 'apple'
  43. SET @Value2 = 'APPLE'
  44. SET @RESULT = ''
  45. IF ((@Value1 + 'EndOfString' = @Value2 + 'EndOfString' COLLATE SQL_Latin1_General_CP1_CS_AS) OR (ISNULL(@Value1, @Value2) IS NULL))
  46. SET @RESULT = 'Equal' ELSE SET @RESULT = 'Not Equal'
  47. PRINT 'Test 6: NVARCHAR. Case sensitive. Cases differ. Expected: Not Equal. Actual: ' + @RESULT
  48.  
  49. SET @Value1 = 'apple'
  50. SET @Value2 = 'APPLE   '
  51. SET @RESULT = ''
  52. IF ((@Value1 + 'EndOfString' = @Value2 + 'EndOfString' COLLATE SQL_Latin1_General_CP1_CI_AS) OR (ISNULL(@Value1, @Value2) IS NULL))
  53. SET @RESULT = 'Equal' ELSE SET @RESULT = 'Not Equal'
  54. PRINT 'Test 7: NVARCHAR. Second value has trailing spaces. ANSI padding quirk workaround applied. Case insensitive. Cases differ. Expected: Not Equal. Actual: ' + @RESULT
  55.  
  56. SET @Value1 = 'apple'
  57. SET @Value2 = 'APPLE   '
  58. SET @RESULT = ''
  59. IF ((@Value1 + 'EndOfString' = @Value2 + 'EndOfString' COLLATE SQL_Latin1_General_CP1_CS_AS) OR (ISNULL(@Value1, @Value2) IS NULL))
  60. SET @RESULT = 'Equal' ELSE SET @RESULT = 'Not Equal'
  61. PRINT 'Test 8: NVARCHAR. Second value has trailing spaces. ANSI padding quirk workaround applied. Case sensitive. Cases differ. Expected: Not Equal. Actual: ' + @RESULT
  62.  
  63. SET @Value1 = 'apple'
  64. SET @Value2 = 'apple   '
  65. SET @RESULT = ''
  66. IF ((@Value1 + 'EndOfString' = @Value2 + 'EndOfString' COLLATE SQL_Latin1_General_CP1_CS_AS) OR (ISNULL(@Value1, @Value2) IS NULL))
  67. SET @RESULT = 'Equal' ELSE SET @RESULT = 'Not Equal'
  68. PRINT 'Test 9: NVARCHAR. Second value has trailing spaces. ANSI padding quirk workaround applied. Case sensitive. Cases identical. Expected: Not Equal. Actual: ' + @RESULT
  69.  
  70. SET @Value1 = 'apple'
  71. SET @Value2 = 'APPLE   '
  72. SET @RESULT = ''
  73. IF ((@Value1 = @Value2 COLLATE SQL_Latin1_General_CP1_CI_AS) OR (ISNULL(@Value1, @Value2) IS NULL))
  74. SET @RESULT = 'Equal' ELSE SET @RESULT = 'Not Equal'
  75. PRINT 'Test 10: NVARCHAR. Second value has trailing spaces. ANSI padding quirk with no workaround. Case insensitive. Cases differ. Expected: Equal. Actual: ' + @RESULT
  76.  
  77. SET @Value1 = 'apple'
  78. SET @Value2 = 'APPLE   '
  79. SET @RESULT = ''
  80. IF ((@Value1 = @Value2 COLLATE SQL_Latin1_General_CP1_CS_AS) OR (ISNULL(@Value1, @Value2) IS NULL))
  81. SET @RESULT = 'Equal' ELSE SET @RESULT = 'Not Equal'
  82. PRINT 'Test 11: NVARCHAR. Second value has trailing spaces. ANSI padding quirk with no workaround. Case sensitive. Cases differ. Expected: Not Equal. Actual: ' + @RESULT
  83.  
  84. SET @Value1 = 'apple'
  85. SET @Value2 = 'apple   '
  86. SET @RESULT = ''
  87. IF ((@Value1 = @Value2 COLLATE SQL_Latin1_General_CP1_CS_AS) OR (ISNULL(@Value1, @Value2) IS NULL))
  88. SET @RESULT = 'Equal' ELSE SET @RESULT = 'Not Equal'
  89. PRINT 'Test 12: NVARCHAR. Second value has trailing spaces. ANSI padding quirk with no workaround. Case sensitive. Cases identical. Expected: Equal. Actual: ' + @RESULT
  90.  
  91.  
  92. --SELECT CONVERT (varchar, SERVERPROPERTY('collation'));
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top