CODEWITHSUNDEEP
sqlpostgresqlpattern-matchingunaccent
Dougui
Dougui

Reputation: 7232

Ignore special characters in the WHERE clause

I have a table named artists with a record with the value 'Miró' in the name column. When I do this request:

SELECT "artists".* FROM "artists" WHERE name = 'Miró'

I have one result, so it works.

Now, when I do this request (without the special ó) :

SELECT "artists".* FROM "artists" WHERE name = 'Miro'

I don't find anything. I want to ignore the special char. Is there a way to do it?
I have postgres 9.1.9.

Upvotes: 2

Views: 3337

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656241

For a more targeted pattern matching, you can use the function unaccent(), provided by the additional module unaccent:

SELECT * FROM artists WHERE unaccent(name) = 'Miro';

To make this fast, create a functional index. You have to overcome the obstacle that the function is only STABLE, not IMMUTABLE. I wrote a comprehensive answer with instructions (including installation) and links recently:
Does PostgreSQL support "accent insensitive" collations?

Upvotes: 4

JDCartee
JDCartee

Reputation: 706

You could try using LIKE instead...

SELECT "artists".* FROM "artists" WHERE name like 'Mir%'

Upvotes: 0

Related Questions