Foliotek Developer Blog

SQL Query Not Returning All Results When Matching on Multiple Search Terms - Space Character Issue

The Problem

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 rowName of#table#row
Notice that the first space was not replaced, while the others were. Once I noticed this, I cast the Name column as a varbinary:

 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).

The Solution

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.