CODEWITHSUNDEEP
sql-serverstringt-sqlpatindextrailing-whitespace
aditya
aditya

Reputation: 15

Explain where trailing spaces are and aren't ignored in SQL Server

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?

Upvotes: 0

Views: 684

Answers (1)

Thom A
Thom A

Reputation: 95561

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.

Upvotes: 1

Related Questions