How to do SQL query that ignores uppercase, lowercase and accents?

Navigation
10

I have a database with multiple movies, and would like to create a SQL query that ignores the presence of uppercase, lowercase, accents, and ç. For example when searching for "brave heart" I would like the query to return the record of the movie Braveheart. But I do not know how to do it, currently I have used 'like' to return records that find the word typed in any position. 

select * from filmes where titulo like '%Valente%'

I use PostgreSQL as a DBMS and I am starting the studies with SQL language and relational database modeling. Thanks in advance for your cooperation, any help is welcome.

    
asked by anonymous 08.11.2015 / 04:16

1 answer

12

To do a case-sensitive search, change LIKE to ILIKE .

The ILIKE operator is PostgreSQL-specific and its behavior is similar to LIKE . The only difference is that it is case-insensitive , meaning it is not case-sensitive.

To ignore accents install the extension, unaccent it is available from postgres 9.1 forward, open pgAdmin or similar and type 

CREATE EXTENSION unaccent;

The query should look like this: 

SELECT * FROM filmes WHERE unaccent(titulo) ilike unaccent('%Valente%')

Reference

Does PostgreSQL support "accent insensitive" collations?

    
08.11.2015 / 04:25
Re-write and close array of a file Number of characters of CPF, CNPJ and RG