I ran into an obscure issue today with a SQL Query that seemed to be failing to match on multiple words. For instance, it would match correctly on “search” but not on “search terms”. The query returned some matches but not all.
After a good bit of investigation, I finally realized that some of the data being matched on contained non-standard space characters, specifically an encoded non-breaking space. The way I originally detected this was to simply replace spaces with the pound sign (#).
SELECT Name, REPLACE(TheName, ' ', '#') FROM MyTable /* not sure why, but REPLACE was causing formatting issues, so I added the _ afterwards, and it fixed it. */
From the results, I saw that not all the spaces were being replaced correctly. I was getting something like this:
|Name with spaces replaced||Name|
|Name of table row||Name of#table#row|
SELECT CAST(Name as varbinary(250)) FROM MyTable
This yielded the following value:
N a m e o f t a b l e r o w 0x4E 61 6D 65 20 6F 66 A0 74 61 62 6C 65 A0 72 6F 77
Notice that the last two spaces are the code A0 (the non-breaking space) rather than 20 (standard space).
Once I was able to find this, the solution was pretty easy. Since the non-breaking spaces were not intended, all I had to do was change them to standard spaces, and I accomplished this with the following query (Character 160 is the non-breaking space):
UPDATE MyTable SET Name = REPLACE(Name, CHAR(160), ' ')
After this, the matching on multiple terms worked as desired.