oracle_sql_regex.sql

select
CASE
WHEN (REGEXP_SUBSTR(category, '(a[[:alnum:]]|sa)', 1, 1, 'i') is not null) THEN 'SUITE'
WHEN (REGEXP_SUBSTR(category, '(d[[:digit:]]|sd)', 1, 1, 'i') is not null) THEN 'DELUXE_BALCONY'
WHEN (REGEXP_SUBSTR(category, '(b[[:alnum:]]|sb)', 1, 1, 'i') is not null) THEN 'BALCONY'
WHEN (REGEXP_SUBSTR(category, '(c|d|e|f|g|h)[[:alnum:]]?', 1, 1, 'i') is not null) THEN 'OCEANVIEW'
ELSE 'INTERIOR'
END AS META
from foo