Snowflake regex with rlike to return a bool gives unexpected result

Want a bool back 1 or 0 if field ‘name’ contains string ‘IOS_PROD’. Tried:

case when rlike(c.name, 'IOS_PROD') then 1 else 0 end as IOS

Returns all 0s instead of a mix of 1s and 0s as expected. How can I regex and return 1 or 0 in this way?

Verified the underlying data with:

select name, count(1) from table group by 1

For sure, there are mixes of cases where name contains ‘IOS_PROD’ and does not, so it’s my regex that’s flawed.

Answer

First, if this returns 0s, then there is not match.

Second, you can use the built-in SQL LIKE operator for this:

(case when c.name like '%IOS_PROD%' then 1 else 0 end) as IOS

One problem could be the uppercase versus lowercase, so perhaps:

(case when upper(c.name) like '%IOS_PROD%' then 1 else 0 end) as IOS