SQL LIKE using special character

Question

How do I get a query that brings me word that contains or does not the special character? Eg, I have this data: "NÃO" and if I search by typing "NAO", you should return this information to me. And the converse too, if I have: "ANTONIO" and I write "ANTÓNIO," ANTÓNIO should return to me. I use this code but it does not work:

SELECT * FROM PESSOA WHERE NOME like '%'+ @PROCURAR + '%'

Give this a try, https://stackoverflow.com/questions/2461522/how-do-i-perform-an-accent-insensitive-compare-e-with-è-é-ê-and-ë-in-sql-ser — Ola Ekdahl, Sep 22 '17 at 01:37
A bit of a hack, but you could compare to `REPLACE(NOME, 'Ã', 'A')` — Tim Biegeleisen, Sep 22 '17 at 01:39

Jino Shaji · Accepted Answer · 2017-09-22T02:38:11.550

Accent Sensitive and Accent Insensitive searching can be don by using Latin1_general_CI_AI

ie, ÃNTONIO and ANTONIO are the same if Accent Insensitive.

In the below query Latin1_general_CI_AI can be break down into the following parts.

latin1 makes the server treat strings using charset latin 1, basically ascii.
CI specifies case-insensitive, so "ABC" equals to "abc".
AI specifies accent-insensitive,so 'ü' equals to 'u'.

Your query should be as follows:

SELECT * FROM table_name WHERE field_name COLLATE Latin1_general_CI_AI Like '%ANTONIO%' COLLATE Latin1_general_CI_AI

Expected Result is as follows:

 Id  name
 1  ÃNTONIO 
 2  ANTÓNIO 
 3  ANTONIO 
 4  ANTÓNIÓ

score 1 · Answer 2 · answered Sep 22 '17 at 05:54

SELECT * 
FROM PESSOA 
WHERE NOME COLLATE Latin1_General_CI_AI Like '%'+ @PROCURAR + '%' 
COLLATE Latin1_General_CI_AI

for please visit Latin1_General_CI_AI

in sql server, what is: Latin1_General_CI_AI versus Latin1_General_CI_AS

see also https://www.mssqltips.com/sqlservertip/4395/understanding-the-collate-databasedefault-clause-in-sql-server/

SQL LIKE using special character

2 Answers2