0

I believed trailing spaces are ignored in SQL everywhere until I encountered a scenario with PATINDEX where they aren’t ignored.

Case 1: SELECT PATINDEX('qwerty', 'qwerty') returns 1.
Case 2: SELECT PATINDEX('qwerty', 'qwerty ') returns 1. A trailing space inserted at the end of the second string is ignored.
Case 3: SELECT PATINDEX('qwerty ', 'qwerty') returns 0.

Why it didn’t ignore the trailing space inserted at the end of the first string just like Case 2?

GMB
  • 216,147
  • 25
  • 84
  • 135
aditya
  • 15
  • 4

1 Answers1

1

I believed trailing spaces are ignored in SQL everywhere

No, they are not. They are ignored for comparison purposes only, that's it. So 'mystring ' and 'mystring' would be treated the same in the clause WHERE 'mystring' = 'mystring '.

They are not, however in things like using PATINDEX.

Note that it is documented in the LEN function that trailing spaces are also ignored. DATALENGTH on the other hand, includes them.

Thom A
  • 88,727
  • 11
  • 45
  • 75
  • Exactly my question. Trailing spaces aren't ignored in PATINDEX but ignored at other places like WHERE, LEN, etc. Trailing spaces are inserted to complete the length while storing a value in a table so not ignored in DATALENGTH. Are there any other places where they are not ignored? How would I identify, is there a rule or something? – aditya May 19 '20 at 21:50