T-SQL: Change Collation on your LIKE Clause Using COLLATE

Ever have trouble matching unicode letter equivalents in your T-SQL queries? For example, when you want “Québec” and “Quebec” to be equivalent in your text search query?

This is where specifying Accent Insensative Collation (The “AI” in all those collation codes) comes to the rescue.  If your database is already set to an AI collation, then you are all set already, but if not, change your LIKE statement to use something like the following:

 
SELECT *
FROM MyTable (NOLOCK)
WHERE MyColumn COLLATE SQL_Latin1_General_CP1_CI_AI LIKE '%quebec%'

Hope this helps!

This entry was posted in MS SQL Server, T-SQL. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *