SQL Server | Comparing strings with whitespace

Ran into an annoying issue today dealing with string comparison in SQL Server. At first I thought it had to do with additional whitespace at the end of my string. What I was seeing was 'string ' was not equaling 'string' in my query. But then after further research I found that this shouldn't actually be the case. In SQL Server, these two values should be equal since SQL Server follows the ANSI/ISO SQL-92 specification (Section 8.2, , General rules #3) which states that both strings need to be equal in length before comparing them. To achieve this, SQL Server will pad the shorter string so that its length is equal to the longer string. It's important to note that SQL Server will only pad our strings at the end with whitespace, not at the start.

So why was my situation giving me a different results? Well, frustratingly enough it turns out what I thought was extra whitespace at the end of my string wasn't actually whitespace at all. It was a unicode character c2a0. Which translates to 'Â '. But this unicode character was not being visually represented in my string. I had to copy the string into a converter that spit out the hex version of that string. Where I went wrong is a copied this string from a shared Postman collection directly into TablePlus.

Resources

• https://dba.stackexchange.com/questions/10510/behavior-of-varchar-with-spaces-at-the-end
• https://learn.microsoft.com/en-us/sql/t-sql/language-elements/string-comparison-assignment?view=sql-server-ver16

Struggling on a piece of this new feature..

What I am up against is I need to populate a single table on the frontend with records that are coming from two database tables. These records are related via a One To Many relationship. One Payment is made up of many smaller GroupMemberPayments.

The challenges this update has are;

• Needs to retain the current filtering features that are available
• Records need to be ordered by a timestamp
• Support pagination

The pagination requirement is the piece that's presenting the biggest hurdle. My current approach is aggregating my data from these 2 tables but have that come from a single Eloquent query so that I can easily paginate those results. Unfortunately, I don't think it would be a wise to query each table independently and then attempt to merge the two datasets together. Having a single query would allow for filtering, ordering and pagination to happen more easily.