SQL equivalent of big query REGEXP_CONTAINS in a CASE WHEN statetement

Question

I have a CASE WHEN written in Big Query which uses REGEXP_CONTAINS function and is as below

CASE 
  WHEN REGEXP_CONTAINS(web_url, r"^\/landing.*$|^\/lp.*$|^\/pages.*$") THEN 'Lander'
  WHEN REGEXP_CONTAINS(web_url, r"^\/chemist\/.*$|^\/chemical\/.*$|^\/chemists.*$")
            THEN 'Chemists'
 ELSE 'Others'
 END
            

I am trying to write the equivalent without using REGEXP_CONTAINS and so far I have managed to write is as below:

  CASE WHEN (page_path LIKE '/landing%'
             OR page_path LIKE '/lp%'
             OR page_path LIKE '/pages%'
             ) THEN "Lander"

       WHEN (page_path LIKE '/chemist%'
             OR page_path LIKE '/chemical%'
             OR page_path LIKE '/chemists%'
             ) THEN 'Chemists'

 ELSE 'Others'
 END
            

I am getting similar results with both CASE statements for 'Lander' column but for 'Chemists' column, they dont seem to match.

Can someone help with what changes I need to make?

Answer 1

Posting this suggestion as an Community WIki for better visibility for the community, as @eshirvana suggest appropriate use of wildcard and escape characters:

Shouldn't it be like /chemist/% , /chemists% , /chemical/%