CODEWITHSUNDEEP
sqlsql-serverselect
Joaquim Costa
Joaquim Costa

Reputation: 69

SQL LIKE using special character

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 + '%'

Upvotes: 2

Views: 237

Answers (2)

I A Khan
I A Khan

Reputation: 8869

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/

Upvotes: 1

Jino Shaji
Jino Shaji

Reputation: 1103

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Ó

Upvotes: 2

Related Questions