SQL to Snowflake syntax

Question

I am trying convert SQL Server code to snowflake but didn't find anything helpful. How can I write the same logic in snowflake? I tried using regexp_like but it didn't work. Any helpful suggestion will be appreciated.

select * from s_gth 
    where mtext like '%[^a-z]BITS[^a-z]%' 
    OR mtext like 'BITS[^a-z]%' 
    OR mtext like '%[^a-z]BITS' 
    OR mtext like 'BITS'

Answer 1

For a case insensitive search, use the 'i' parameter. Otherwise you may omit that. See also REGEXP_LIKE for more details.

with s_gth (id, mtext) as (
    select * from values
    (1,'aBITSz')
    ,(2,'a BITS')
    ,(3,'BITS0z')
    ,(4,'BITS')
    ,(5,'KIBBLE')
)
select * from s_gth 
    where REGEXP_LIKE(mtext, '.*[^a-z]BITS[^a-z].*', 'i') 
    OR REGEXP_LIKE(mtext, 'BITS[^a-z].*', 'i')
    OR REGEXP_LIKE(mtext, '.*[^a-z]BITS', 'i')
    OR REGEXP_LIKE(mtext, 'BITS', 'i')

ID	MTEXT
2	a BITS
3	BITS0z
4	BITS