Strong recommendation #1: Don't store lists of comma separated values!
Store each value as a separate row in a table. This makes it much easier to ensure you can only use specific values.
You can do this using check constraints:
create table subcategories (
subcat varchar2(10) not null primary key
);
alter table subcategories add constraint valid_categories_c check (
subcat in ('subcat1','subcat2','subcat3','subcat4')
);
insert into subcategories values ('subcat1');
insert into subcategories values ('subcat2');
insert into subcategories values ('subcat7');
SQL Error: ORA-02290: check constraint (CHRIS.VALID_CATEGORIES_C) violated
No need for messy regexes!
Or, assuming your tables will have other columns you're applying subcategories to have a lookup table of subcategories like the one above. Then have a foreign key from your table to these.
This ensures you can only insert values that exist in the lookup:
create table categories (
cat varchar2(10) not null,
subcat varchar2(10) not null references subcategories (subcat)
);
insert into categories values ('cat1', 'subcat1');
insert into categories values ('cat1', 'subcat2');
insert into categories values ('cat1', 'subcat7');
SQL Error: ORA-02291: integrity constraint (CHRIS.SYS_C0016021) violated - parent key not found
insert into categories values ('cat2', 'subcat1');
/* subcat3 not in subcategories table */
insert into categories values ('cat2', 'subcat3');
SQL Error: ORA-02291: integrity constraint (CHRIS.SYS_C0016021) violated - parent key not found
Storing values separately will make many of your queries easier to write too.
If you need to get the data back out as csv for display, this is easy to do with listagg:
select cat,
listagg(subcat, ',') within group (order by subcat) subcats
from categories
group by cat;
CAT SUBCATS
cat1 subcat1,subcat2
cat2 subcat1
If for some reason you insist you must store comma separated lists, you can:
- Get the list of valid strings and your separator
- Add a check constraint only allowing those strings that don't contain something not in that list
So your regex is:
[^(subcat1|subcat2|subcat3|,)]
And the rows you allow are those where the NOT regexp_like of this:
drop table categories;
delete subcategories;
alter table subcategories drop constraint valid_categories_c;
alter table subcategories modify (subcat varchar2(50));
alter table subcategories add constraint valid_categories_c check (
not regexp_like(subcat, '[^(subcat1|subcat2|subcat3|,)]')
);
insert into subcategories values ('subcat1,subcat2');
insert into subcategories values ('subcat3,subcat4');
insert into subcategories values ('subcat1,subcat7');
select *
from subcategories;
SUBCAT
subcat1,subcat2