CODEWITHSUNDEEP
sqlsql-serverjoindiacritics
Greg
Greg

Reputation: 205

Accent Insensitive Join SQL Server

I am looking to join two tables by a common string ignoring accents and capitalization.

I've been looking for an answer to this question and can't figure it out. I am working with a dirty database and I need to be able to join two tables by a varchar column where some of the rows have accents and some do not.

Below is what I have so far but does not execute due to incorrect syntax. Thank you for your help!

SELECT p.product_id, VarDim.dimension_id 
FROM product p 
LEFT JOIN Dimension dim on COLLATE Latin1_general_CI_AI dim.[Description] = COLLATE Latin1_general_CI_AI p.shortdesc

Upvotes: 1

Views: 1140

Answers (1)

Mitch Wheat
Mitch Wheat

Reputation: 300529

You just need to place a single COLLATE to the right of the joined columns:

SELECT p.product_id, VarDim.dimension_id 
FROM product p 
LEFT JOIN Dimension dim on dim.[Description] = p.shortdesc COLLATE Latin1_general_CI_AI

Upvotes: 2

Related Questions