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?
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 clauseWHERE '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.