The [regexp](https://community.denodo.com/kb/view/document/Oracle%20SQL%20to%20Denodo%20VQL%20Quick%20Reference?category=VQL) function similar to regexp_replace, and like keyword are available in denodo for you to use, and I believe are well suited to what you’re doing.
For the regexp_like, replacing it with 1 or 3 like statements is simple enough
CASE WHEN upper(MAIN_PRODUCT) like ‘CBM%’’
CASE WHEN upper(MAIN_PRODUCT) like ‘CBMCLS’
OR upper(MAIN_PRODUCT) like ‘CBMOT’
For regexp_replace, see the above documentation.
Hope this helps!