"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
[Advertisement] BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!