"There is a small coalition of developers at the office who are vehemently anti-database," writes Bob, "naturally, this faction also doesn't value 'experience' -- mostly, because they have none. At least, not outside their university studies. They'll often liken a database server to a file system, and suggest that it's just a convenient way to store blobs of data -- but everything else is inelegant bloat."
"Of course, since this coalition doesn't have any actual decision making power, their strong opinions remain just that, and we stick with a standard tech of technology. Of course, their attitude often leaks into the code, as in this example of a simple search query that was used to search for an employee by name. The trick is we have employees with accented characters in our directory but, if a user search for the name without the accented char (e instead of é), it needs to return the result anyway."
SELECT * FROM Persons WHERE /* HACK! Being SQL, there's no concept of RegEx, so we have to this horrible hack */ Replace(Replace(Replace(Replace( Replace(Replace(Replace(Replace( Replace(Replace(Replace(Replace( Replace(Replace(Replace(Replace(name, ' ',''), 'É','E'),'È','E'),'Ê','E'),'Ë','E'), 'À','A'),'Â','A'),'Ä','A'), 'Ï','I'),'Î','I'), 'Ç','C'), 'Ô','O'),'Ö','O'), 'Ü','U'),'Ù','U'),'Û','U') LIKE @name AND /* HACK AGAIN! Oh yeah, no cod reuse. Why are we doing this in SQL!? */ Replace(Replace(Replace(Replace( Replace(Replace(Replace(Replace( Replace(Replace(Replace(Replace( Replace(Replace(Replace(Replace(surname, ' ',''), 'É','E'),'È','E'),'Ê','E'),'Ë','E'), 'À','A'),'Â','A'),'Ä','A'), 'Ï','I'),'Î','I'), 'Ç','C'), 'Ô','O'),'Ö','O'), 'Ü','U'),'Ù','U'),'Û','U') LIKE @surname ORDER BY name, surname
Bob added, "like most of their code, I was able to replace it with a much simpler block."
SELECT * FROM Persons WHERE name collate Latin1_General_CI_AI LIKE @name AND surname collate Latin1_General_CI_AI LIKE @surname ORDER BY name, surname