CosmosDB accent (diacritics) insensitive query-CodePudding

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?

CodePudding user response：

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

CodePudding user response：

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