2

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Dougui
  • 7,142
  • 7
  • 52
  • 87
  • Maybe you could just remove the special characters from the string you're searching.. and maybe use `LIKE` instead of `=` .. something like `WHERE name like 'MIR_' – Radu Gheorghiu Jun 18 '13 at 21:13
  • Well, the two characters are different. So, you'd probably need something like `SELECT "artists".* FROM "artists" WHERE name like 'Mir_'` –  Jun 18 '13 at 21:13
  • I think that there is already a solution existing at stackoverflow: http://stackoverflow.com/questions/11005036/does-postgresql-support-accent-insensitive-collations – thmshd Jun 18 '13 at 21:20

2 Answers2

4

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?

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
0

You could try using LIKE instead...

SELECT "artists".* FROM "artists" WHERE name like 'Mir%'
JDCartee
  • 696
  • 6
  • 9