CosmosDB accent (diacritics) insensitive query

Question

How do I query CosmosDB ignoring accents? Example:

{"id": "1", "name": "Émpresa 1" }
{"id": "2", "name": "empresa 2" }

SELECT * 
FROM container_name
WHERE name LIKE 'empresa%'

I want to retrieve both records from this query, how can I do that? If it's not possible "out of the box" then is there any workaround?

Can’t you do something like “ SELECT * FROM container_name WHERE name LIKE '%mpresa%'”? — Gaurav Mantri, Sep 14 '21 at 17:06

Sajeetharan · Answer 1 · 2021-09-14T17:59:02.313

0

What you need here is just '%mpresa%' as the first item has starting letter with 'E' while second one has 'e'

SELECT * FROM c WHERE c.name LIKE "%mpresa%"

Demo

 [
    {
        "ItemId": "1",
        "name": "Émpresa 1",
        "id": "c599d43a-a88b-441f-b6df-361380f05eac",
    },
    {
        "ItemId": "2",
        "name": "empresa 2",
        "id": "ed894a28-f439-4a23-88e2-80d29ff106e0",
    }
]

If you want to perform free text search you may consider the feature supported for SQL API with Azure cognitive search here

edited Sep 14 '21 at 17:59

answered Sep 14 '21 at 17:20

Sajeetharan

216,225
63
350
396

this will work for this particular test case, but if he needs a different query or the accent is in another place it won't work – Thiago Custodio Sep 14 '21 at 17:22
you mean like free text search? – Sajeetharan Sep 14 '21 at 17:23
@Sajeetharan Please be sure to only include formatted text, not images of text. This [meta post](https://meta.stackoverflow.com/a/285557/272109) lists many reasons why this is important. Also, no need to include Cosmos DB's internal properties. – David Makogon Sep 14 '21 at 17:42
@DavidMakogon Sure, I have been always pasting the image interms of samples. will keep in mind – Sajeetharan Sep 14 '21 at 18:02
@Sajeetharan - when you have to illustrate something with a screenshot, then go for it. But in the case of code, queries, data, etc, this is where it's important to use formatted text, especially for those who use screen readers (and that's just one of many reasons enumerated in that meta post). – David Makogon Sep 14 '21 at 18:11

score 0 · Answer 2 · answered Sep 14 '21 at 17:24

Cosmos DB will save data in the specified encoding of your text. As 'É' and 'e' are not the same, I believe you have two options:

1- Replace special chars to it's root form: https://stackoverflow.com/a/2086575/1384539

2- Use a Search Engine (e.g. Azure Cognitive Search) which will to the previous work for you

CosmosDB accent (diacritics) insensitive query

2 Answers2