'How can I remove case sensitivity from field formulas?

I created a new field in Data Studio whose formula is:

IF(STATUS= "lost","lost","found")

The problem is STATUS column contains lost, Lost and many other variations. How do I make this formula case insensitive in the easiest manner.

I read something about regex and using (?i) but that didn't work as expected.

regexgoogle-data-studiore2


Solution 1:[1]

If in the status field the word "Lost" or "LOST" or "lost" or "losT" is given, the text "lost" should be put out, otherwise "found".

For removing the case sensitivity of a field, no regualar expression is needed. The text of a field can be transfered to lower case by the function lower(). Thus the formula is:

if(lower(STATUS)="ok","found","lost")

If you want to search for the word "lost" in a field, regualar expression is a good choice:

if(REGEXP_MATCH(STATUS,r"(?i).*lost.*"),"found","lost")

The (?i) makes the search case-insensitive. The word lost is needed. The .* matches any character before or after the lost word.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Samuel

Related Questions