About Me

My photo
Northglenn, Colorado, United States
I'm primarily a BI Developer on the Microsoft stack. I do sometimes touch upon other Microsoft stacks ( web development, application development, and sql server development).
Showing posts with label insensitive. Show all posts
Showing posts with label insensitive. Show all posts

Friday, January 06, 2012

Removing accents for string comparisons in tsql

I've been reading The Daily WTF, and found this one interesting: http://thedailywtf.com/Articles/The-AntiSQL-Coalition-.aspx

By using the Latin1_General_CI_AI   (AI stands for Accent Insensitive)
the user was able to by-pass special characters like: É,È,Ê, and Ë for E when doing a search.
Example:

SELECT*
FROMPersons
WHEREname collate Latin1_General_CI_AI LIKE @name
AND surname collate Latin1_General_CI_AI LIKE @surname
ORDERBY name, surname

Additional references:
http://msdn.microsoft.com/en-us/library/ms187582.aspx


P.S. Funny to see other companies have Database haters too.