How to ignore accents in an SQL query?

Asked

Viewed 2,322 times

8

How could I perform a query ignoring words with letters that contain accents? Example of words:

Olá, é, Lógica, Pão

I tried using the command collate noaccents as follows:

select c.* from Curso c 
where c.Descricao like '%Logica%' collate noaccents;

However, I did not get any results, and it only works if I remove the accent from the word Lógica. So how could I ignore the accents?

Minimal example

Commands to create the illustration example.

Table:

create table Curso(Descricao TEXT);

Insert:

insert into Curso(Descricao) values ('Lógica de Programação');

See working on SQL Fiddle.

sql sqlite3 accentuation
  • 1

    you have to link to the accentuation library of your application. https://answall.com/questions/1785/70 - note that I left a code at the end with a simplified solution.

  • 1

    I wonder if it would be duplicate even, despite the title.

  • @Bacco I will take a look, but it seems to me a path that is not worth, maybe I opt for a normalization creating another column, I will have to research. I thought it was simple xD

  • 1

    simple it is, only it is a different path than you thought :D - usually nobody uses Sqlite "alone", and the application almost always has comparison function, just "link" with Sqlite

  • 1

    in particular, you have the sqlite3_create_collation() (and its variants) which is to use the function you want for comparison, usually you will point to the native function of comparison that already uses in other parts of your application (until everything is equal, the DB and the application)

  • @Bacchus to another question answers much of this question in a general way. Anyway I will implement otherwise, I will let the language do the work instead of Sqlite, so just link as you suggested. So if you want you can mark that question as a duplicate of the other and let the community decide, beauty?

  • If you think it is, your vote closes at once. I prefer that you think calmly what you think is best. About linking, read the documentation I mentioned, which is the most common way. Also, it has create_function if you need other "Accent insensitive" situations as well.

  • 1

    Possible duplicate of Sqlite android query() / like with accents and without

  • @RBZ is not duplicated but related

  • @Sorack withdrawn! ;)

  • @cat Any answers solved what was in doubt? Need something else to be improved? Think it is possible to accept it now?

  • @Maniero I need to access the database on another machine, the collation prevents me from accessing the bank using a customer, because I have to keep recreating it. I’m studying the possibility of using an algorithm soundex or similar, and create the keys to increase the accuracy of the research, but I am studying before making the decision.

Show 7 more comments

2 answers

4


  • I’d go that way: How to make "case-insensitive" comparisons in Sqlite?. Is the simple.

  • Note that some implementations of Sqlite already have a way to deal with this, but they no longer have the greater advantage of Sqlite than being a small code. It has the advantage of dealing with more sophisticated cases. But it will be slower. The question does not give a context of use. I talked about it in Sqlite android query() / like with accents and without.

    If your implementation doesn’t have something ready you can put a complete way to deal with it. It has the same or more difficulty than the previous one. In practice you need to know more about what you are doing. You can even make an implementation that is the same size as Sqlite customizing the ICU. It will be very limited, even more than the previous solution, even if it is much larger.

  • The alternative is to create a column without the accents and index it and use it for every search (after taking the accents and making everything tiny), and at the time you take the data itself use the column with the original dice. This takes up a lot more memory and makes everything much slower, but it works. It’s gambiarra, solution of those who do not want to do the right thing.

  • Another solution is to abandon the accents once and only work with ASCII. I find this terrible from the point of view of UX, and very 80’s. But I see systems working like this today. It’s backward.

Sqlite is an excellent, simple-to-use, performance-friendly database, but it’s not something done, it needs a certain effort to do beyond the basics.

  • I forgot that Sqlite can have different implementations depending on where it is used, my failure to have contextualized in a clearer way. I will edit the question ;)

  • I marked as accepted because your answer shows sensible paths, and in the end I adopted the third alternative (is gambiarra I know :( ) but "solved" the problem, I’m still studying the possibility of using a soundex algorithm instead.

1

Just need to set up a Collation using the sqlite3_create_collation and use as follows:

SELECT * FROM Curso WHERE Descricao LIKE "Logica%" COLLATE NOACCENTS
  • 1

    And how to set up this Collation? It could add a little more detail of how to do this?

Browser other questions tagged sql sqlite3 accentuation

You are not signed in. Login or sign up in order to post.