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

Question

I have a database with registration of several films, and I would like to create an SQL query that ignores the presence of capital letters, lowercase, accents and ç. For example when searching for "brave heart" I would like the query to return me the record of the movie Braveheart. However do not know how to do, currently I have used 'like' to return records that find the word typed in any position.

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

Use as DBMS Postgresql and I’m starting studies with SQL language and relational database modeling. From now on I thank everyone for their cooperation, any help will be welcome.

Answer 1

To make an independent search of upper or lower case, change the LIKE for ILIKE.

The operator ILIKE is specific to Postgresql and its behavior is similar to LIKE. The only difference is he’s case-insensitive, that is, does not differentiate between upper and lower case.

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

CREATE EXTENSION unaccent;

The consultation should look like this:

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

Refrencia

Does Postgresql support "Accent insensitive" collations?