Case Insensitive SELECT LIKE

[extarys]

Hello there!

I got a bucket which contain a scientific_name field (?) that may or may not contain uppercase (depending on the document)

Is there a way I can SELECT * FROM bucket WHERE scientific_name LIKE = ‘%myquery%’ and return a string like “MyQuery”? I have 450,000 documents and uppercase characters may vary but it would be important that they stayed that way.

If not I guess I’ll run a script to convert everything to lowercase and find a way to store where the uppercase characters are located.
Thanks!

[geraldss]

Sure.

SELECT *
FROM bucket
WHERE LOWER(scientific_name) LIKE '%myquery%'

[prasad]

Hi @extarys, You may also want to check following blogs to learn special techniques in N1QL to optimize LIKE query perf (especially when using leading wildcards).

• https://dzone.com/articles/new-performance-tricks-with-n1ql
• https://dzone.com/articles/a-couchbase-index-technique-for-like-predicates-wi
• https://dzone.com/articles/more-than-like-efficient-json-search-with-couchbas

-Prasad