Oracle 使用正则表达式指定逗号分隔字符串中允许的值

askTom 2017-04-04

259

问题描述

你好，
我想知道在插入逗号分隔字符串时是否可以指定允许使用哪种值。
例如: 允许的值: subcat1，subcat2，子cat3，subcat4。
我有带有列子类别的表项目，我可以在列中插入多个值，这些值与 ';' 分隔。
我可以插入
子类1; 子类2; 子类3
子类1; 子类2
子类2; 子类1
子cat3
但是我不能插入
subcat7或subcat1;subcat7
or 子cat3; or subcat1;subcat2;

或者有另一个想法来解决这个问题。我了解到oracle是正则表达式定向引擎，这与文本定向引擎有点不同。

谢谢你的回答

专家解答

Strong recommendation #1: 不要存储逗号分隔值的列表!

将每个值存储为表中的单独行。这使得确保您只能使用特定值变得容易得多。

您可以使用检查约束来执行此操作:

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
复制

不需要凌乱的正则表达式!

或者，假设您的表将具有其他列，则您正在应用子类别以具有类似于上面的子类别的查找表。然后有一个外键从你的表到这些。

这确保您只能插入查找中存在的值:

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复制

单独存储值将使您的许多查询也更容易编写。

如果您需要将数据返回为csv进行显示，这很容易使用listagg:

select cat, 
       listagg(subcat, ',') within group (order by subcat) subcats
from   categories
group  by cat;

CAT   SUBCATS          
cat1  subcat1,subcat2  
cat2  subcat1   复制

如果由于某种原因你坚持你必须存储逗号分隔的列表，你可以:

-获取有效字符串列表和您的分隔符
-添加一个检查约束，仅允许那些不包含该列表中的内容的字符串

所以你的正则表达式是:

[^(subcat1|subcat2|subcat3|,)]复制

你允许的行是那些不是regexp_like的行:

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  
复制