How to compare ignoring case with IN keyword

Question

In Cosmos Scope / SQL how to compare a column country with a list of value ignoring case.

SELECT * FROM student WHERE student_name IN ("aLpHa", "BetA", "GamMa")

It is odd that a column "country" would have the name `student_name`. — Gordon Linoff, Apr 26 '19 at 20:13
Possible duplicate of [How to do a Case Insensitive search on Azure DocumentDb?](https://stackoverflow.com/questions/30512806/how-to-do-a-case-insensitive-search-on-azure-documentdb) — David Makogon, Apr 27 '19 at 11:07

Sajeetharan · Answer 1 · 2019-04-27T18:38:27.793

2

The following query should work in cosmosdb

select * from json j where LOWER(j.student_name) IN ("alpha", "beta", "gamma")

You can have a UDF as well,

udf:

function convertLower(str){
    return str .toLowerCase();
}

and use it as

SELECT * FROM c where udf.lowerConvert(c.student_name) IN ("alpha", "beta", "gamma")

edited Apr 27 '19 at 18:38

answered Apr 27 '19 at 04:33

Sajeetharan

1

As mentioned in the other question (and Aravind's answer) I linked to as duplicate, `lower()` is not efficient, as it will end up scanning an entire partition (or all of the collection, if you enable a cross-partition query), since it completely bypasses any indexing. – David Makogon Apr 27 '19 at 11:08
Documents are also showing it should work. This is not even running in iScope. What are the tools you are using? Sorry if it is silly. I am new to Cosmos environment. – pcbabu Apr 29 '19 at 18:40

score 1 · Answer 2 · answered Apr 26 '19 at 19:10

1

You can use lower():

SELECT * FROM student WHERE lower(student_name) IN ("alpha", "beta", "gamma")

Or alternatively, user upper() respectively.

answered Apr 26 '19 at 19:10

Nir Alfasi

Do you have any idea how I can do the same thing in cosmos db? – pcbabu Apr 26 '19 at 20:59
@pcbabu I'm not familiar with it, but if it doesn't work you can also try using `ilike` for comparison (it's same as `like` only case-insensitive): `SELECT * FROM student WHERE student_name ilike "alpha"` – Nir Alfasi Apr 27 '19 at 03:37
Also, according to [this](https://stackoverflow.com/a/30513004/1057429) you can use `lower()` – Nir Alfasi Apr 27 '19 at 03:40
FYI `lower()` results in a partition (or collection) scan, since it can't use indexes on the properties being searched. More details in the answer to the question I linked as duplicate. – David Makogon Apr 27 '19 at 11:09

2 Answers2