SQL Server find accented Latin word using LIKE

Question

Table Data

----------------------------
jalapeño jam
----------------------------
jalapeño jams
----------------------------

Finding data with like 'jalapeñ' is the result gives successfully.

But when I am finding with 'jalapen' It does not give any result.

My Query

SELECT TOP 50 Id,Name AS Keyword
FROM Keywords (NOLOCK)
WHERE Name like N'%jalapen%'`

Answer 1

Your column seems to be using an accent sensitive collation. You need to use an accent-insensitive collation. You can force that in the query by adding the COLLATE keyword and an appropriate collation, such as Latin1_General_100_CI_AI, such as:

SELECT TOP 50 Id,Name AS Keyword
FROM Keywords WITH (NOLOCK) -- at least add the "WITH", but best to not use at all
WHERE Name like N'%' + @Keyword + N'%' COLLATE Latin1_General_100_CI_AI;

In the collation name, the _AI means "Accent INsensitive", just like the _CI means "Case INsensitive".