我要评论1. 测试数据准备
-- 可重复执行：先删旧表
drop table test_employee_skills purge;
create table test_employee_skills (
emp_id varchar2(10) primary key,
emp_name varchar2(50),
dept_name varchar2(50),
skill_list varchar2(200),
project_list varchar2(500)
);
insert into test_employee_skills values ('e001', '张三', '技术部', 'java,python,sql', '项目a,项目b');
insert into test_employee_skills values ('e002', '李四', '技术部', 'python,javascript,html', '项目c');
insert into test_employee_skills values ('e003', '王五', '产品部', 'axure,sql,python', '项目a,项目d,项目e');
insert into test_employee_skills values ('e004', '赵六', '设计部', 'photoshop,illustrator', '项目f');
insert into test_employee_skills values ('e005', '孙七', '技术部', 'java,spring,mysql,redis', '项目b,项目g,项目h');
insert into test_employee_skills values ('e006', '周八', '产品部', 'sql,tableau', '项目a,项目i');
insert into test_employee_skills values ('e007', '吴九', '技术部', null, '项目j');
insert into test_employee_skills values ('e008', '郑十', '设计部', 'photoshop', '');
注意：e008 的 project_list 写的是空字符串，但 oracle 中 '' 等价于 null（零长度字符即 null，这是 oracle 与 mysql/sql server 的关键差异）。因此它实际存的是 null：where project_list = '' 查不到它，判空必须用 is null。
测试数据预览：
| emp_id | emp_name | dept_name | skill_list | project_list |
|---|---|---|---|---|
| e001 | 张三 | 技术部 | java,python,sql | 项目a,项目b |
| e002 | 李四 | 技术部 | python,javascript,html | 项目c |
| e003 | 王五 | 产品部 | axure,sql,python | 项目a,项目d,项目e |
| e004 | 赵六 | 设计部 | photoshop,illustrator | 项目f |
| e005 | 孙七 | 技术部 | java,spring,mysql,redis | 项目b,项目g,项目h |
| e006 | 周八 | 产品部 | sql,tableau | 项目a,项目i |
| e007 | 吴九 | 技术部 | null | 项目j |
| e008 | 郑十 | 设计部 | photoshop | null（'' 即 null） |
2. 判断是否包含
需求：查 skill_list 是否包含某个技能。核心思路是"前后补逗号后精确匹配"，避免把 java 误配成 javascript。
2.1 instr（推荐，性能最好）
select emp_id, emp_name, dept_name, skill_list
from test_employee_skills
where instr(',' || skill_list || ',', ',python,') > 0;
执行结果：
| emp_id | emp_name | dept_name | skill_list |
|---|---|---|---|
| e001 | 张三 | 技术部 | java,python,sql |
| e002 | 李四 | 技术部 | python,javascript,html |
| e003 | 王五 | 产品部 | axure,sql,python |
原理：字段前后各加逗号得到 ,java,python,sql,，目标值也加逗号 ,python,，再用 instr 找位置。null 拼接后仍为 null，instr 返回 null 不匹配——三个内建函数方案对 null 都天然安全，无需额外过滤。
优点：性能最好——纯字符串函数、无正则解析；null 天然安全，无需过滤。
缺点：需手工前后补逗号，写法略啰嗦；仅支持单值判断，多值需自行组合（见 2.5）。
适用版本：广泛兼容 | 性能：⭐⭐⭐⭐⭐
2.2 like
-- 前后加逗号匹配（推荐，避免误匹配） select emp_id, emp_name, skill_list from test_employee_skills where ',' || skill_list || ',' like '%,java,%'; -- 直接使用通配符（可能误匹配，不推荐） select emp_id, emp_name, skill_list from test_employee_skills where skill_list like '%java%'; -- 会误匹配 'javascript'
注意：%java% 会把 'javascript' 也匹配进来；必须用第一种前后补逗号的写法。
优点：写法简单易读；同为纯字符串操作，性能接近 instr。
缺点：容易漏掉前后补逗号导致误匹配（%java% 会命中 javascript）；语义不如 instr 直观。
适用版本：广泛兼容 | 性能：⭐⭐⭐⭐
2.3 regexp_like
-- 精确匹配（边界控制） select emp_id, emp_name, skill_list from test_employee_skills where regexp_like(skill_list, '(^|,)sql(,|$)'); -- 匹配多个值 select emp_id, emp_name, skill_list from test_employee_skills where regexp_like(skill_list, '(^|,)(java|python)(,|$)');
正则说明：(^|,) 匹配开头或逗号，(,|$) 匹配逗号或结尾，精确圈定独立技能项。多值 (java|python) 为 or（任一匹配）语义；and 写法见 2.5。
注意：技能名含正则元字符（如 .、(）时需转义；10g 起可用。
优点：一个表达式同时支持边界控制与多值（(^|,)(java|python)(,|$)），最灵活。
缺点：正则开销最大（官方博客：正则比其他字符串函数慢）；技能名含元字符时需转义。
适用版本：10g+ | 性能：⭐⭐⭐
2.4 自定义函数（复用）
create or replace function find_in_set(
p_value varchar2,
p_str varchar2,
p_delim varchar2 default ','
) return number as
begin
if p_str is null or p_value is null then
return 0;
end if;
return instr(p_delim || p_str || p_delim, p_delim || p_value || p_delim);
end;
/
-- 用法一：判断是否包含
select emp_id, emp_name, skill_list
from test_employee_skills
where find_in_set('python', skill_list) > 0;
-- 用法二：查看位置（字符位置，非元素序号）
select emp_id, emp_name, skill_list,
find_in_set('python', skill_list) as char_pos
from test_employee_skills;
语义说明：本函数借用 mysql 的 find_in_set 命名以方便记忆，但语义不同——mysql 版本返回 1 基的元素序号，本函数返回的是字符位置（instr 语义）。做判断时只看 > 0 即可，不要当作序号使用。
注意：p_value 若含分隔符会误判；数据量大时每行都调用函数，性能一般。
优点：一处定义、处处复用，业务 sql 最简洁；支持自定义分隔符。
缺点：需先创建函数；返回字符位置（非序号），易与 mysql find_in_set 混淆；每行调用函数，大表性能一般。
适用版本：广泛兼容（需先创建） | 性能：⭐⭐
2.5 多值匹配
-- or：掌握 'java' 或 'python'
select emp_id, emp_name, skill_list
from test_employee_skills
where instr(',' || skill_list || ',', ',java,') > 0
or instr(',' || skill_list || ',', ',python,') > 0;
-- and：同时掌握 'java' 和 'sql'
select emp_id, emp_name, skill_list
from test_employee_skills
where instr(',' || skill_list || ',', ',java,') > 0
and instr(',' || skill_list || ',', ',sql,') > 0;
说明：把 2.1 的 instr 条件用 or/and 组合即可；regexp_like 的多值写法见 2.3 第二个示例。
优点：instr 组合，语义清晰，无正则开销。
缺点：每多一个值就多一条 instr，条件多时冗长；性能随条件数线性下降。
适用版本：广泛兼容 | 性能：⭐⭐⭐⭐⭐
2.6 方案对比
| 方案 | 适用版本 | 性能 | 推荐指数 |
|---|---|---|---|
| instr | 广泛兼容 | ⭐⭐⭐⭐⭐ | ⭐⭐⭐⭐⭐ |
| like | 广泛兼容 | ⭐⭐⭐⭐ | ⭐⭐⭐⭐ |
| regexp_like | 10g+ | ⭐⭐⭐ | ⭐⭐⭐ |
| 自定义函数 | 广泛兼容（需先创建） | ⭐⭐ | ⭐⭐⭐ |
性能说明：拼接列无法走索引，以上均为全表扫描下的相对开销。数据量大、查询频繁时应考虑规范化表设计，而不是依赖 sql 技巧。
3. 一行转多行
需求：把每行 skill_list 拆成多行，每行一个技能。以下方案均假设值中不含分隔符本身——含逗号的值在 csv 语义下天然有歧义，任何方案都无解。
3.1 json_table（12c+，推荐）
select t.emp_id, t.emp_name, t.skill_list, j.skill
from test_employee_skills t,
json_table(
'["' || replace(t.skill_list, ',', '","') || '"]',
'$[*]'
columns skill varchar2(50) path '$'
) j
where t.skill_list is not null;
转换过程：'java,python,sql' → '["java","python","sql"]' → 输出 3 行。
执行结果（部分）：
| emp_id | emp_name | skill_list | skill |
|---|---|---|---|
| e001 | 张三 | java,python,sql | java |
| e001 | 张三 | java,python,sql | python |
| e001 | 张三 | java,python,sql | sql |
优点：性能最优；无层级、递归 hack，写法直观。
缺点：仅 12c+；手工拼接字符串遇 "/\ 报 ora-40441；值含逗号无解（所有方案通病）。
注意（重要） ：这里是手工拼接 json 字符串，值中若含双引号或反斜杠会生成非法 json，报 ora-40441（json syntax error）。测试数据不含这些字符所以正常；真实数据若可能包含，需先 replace 转义（如 " → "）。
适用版本：12c+（12.1.0.2 起） | 性能：⭐⭐⭐⭐⭐
3.2 xmltable（10g+）
-- 标准写法
select t.emp_id, t.emp_name, t.skill_list, x.skill
from test_employee_skills t,
xmltable(
'/rowset/row'
passing xmltype('<rowset><row>' || replace(t.skill_list, ',', '</row><row>') || '</row></rowset>')
columns skill varchar2(50) path '.'
) x
where t.skill_list is not null;
-- 简写（12c+ 隐式转换，字符串表达式直接作 xquery）
select t.emp_id, t.emp_name, t.skill_list, x.skill
from test_employee_skills t,
xmltable(
('"' || replace(t.skill_list, ',', '","') || '"')
columns skill varchar2(50) path '.'
) x
where t.skill_list is not null;
注意：两种写法都要处理 xml 特殊字符——值中含 &、< 等会报 ora-19112（xquery 求值错误），需先转义（如 & → &）。简写只是语法糖，并不比标准写法更安全，值中同样不能含双引号。
优点：10g 起可用，兼容旧库；标准写法直观。
缺点：特殊字符需手工转义（&、< 等，否则 ora-19112）；实测性能明显更慢（约 10 倍）；简写仅 12c+ 且难读。
适用版本：标准写法 10g+；简写 12c+ | 性能：⭐⭐⭐
3.3 regexp_substr + connect by（10g+）
select t.emp_id, t.emp_name, t.skill_list,
trim(regexp_substr(t.skill_list, '[^,]+', 1, level)) as skill
from test_employee_skills t
where t.skill_list is not null
connect by level <= regexp_count(t.skill_list, ',') + 1
and prior t.emp_id = t.emp_id
and prior sys_guid() is not null;
关键点：
-
regexp_substr(字段, '[^,]+', 1, level)：取第 level 个逗号分隔值，level 即元素序号 -
prior t.emp_id = t.emp_id：把层级树限制在每行内部，避免行间笛卡尔爆炸 -
prior sys_guid() is not null：官方推荐做法（oracle 官方 sql 博客）——每行 guid 唯一，可避开 connect by 的循环检测；旧资料常见的dbms_random.value 亦可，但官方用 sys_guid - 连续逗号（空元素）会被跳过：
'a,,b' 只出 2 行，不会报错
注意：层级机制本身较重，大表不推荐（见 3.5 性能对比）。
优点：写法简洁；10g 起可用。
缺点：prior+sys_guid hack 难懂；层级机制重，大表慢（实测约为行生成器 5 倍耗时）；regexp_count 仅 11g+。
适用版本：10g+（示例用 regexp_count 需 11g+；10g 改用 length-replace 计数） | 性能：⭐⭐⭐
3.4 lateral（12c+）
select t.emp_id, t.emp_name, t.skill_list, l.lvl, l.skill
from test_employee_skills t,
lateral (
select level as lvl,
trim(regexp_substr(t.skill_list, '[^,]+', 1, level)) as skill
from dual
connect by level <= regexp_count(t.skill_list, ',') + 1
) l
where t.skill_list is not null;
说明：把 3.3 的层级逻辑搬进 dual 上的子查询，用 lateral 逐行关联。无需 prior 技巧、无 json/xml 转义问题，语法清晰；oracle 官方博客处理"列中分隔值转行"用的就是这条路线。
优点：语法清晰、官方博客同款路线；无 prior hack、无转义问题；性能良好（行生成器）。
缺点：仅 12c+；嵌套子查询逐行执行，极端大表需实测。
适用版本：12c+ | 性能：⭐⭐⭐⭐
3.5 方案对比
| 方案 | 适用版本 | 语法简洁度 | 性能 | 推荐指数 |
|---|---|---|---|---|
| json_table | 12c+ | ⭐⭐⭐⭐ | ⭐⭐⭐⭐⭐ | ⭐⭐⭐⭐⭐ |
| lateral | 12c+ | ⭐⭐⭐⭐⭐ | ⭐⭐⭐⭐ | ⭐⭐⭐⭐⭐ |
| xmltable | 10g+（简写 12c+） | ⭐⭐⭐⭐ | ⭐⭐⭐ | ⭐⭐⭐ |
| regexp+connect by | 10g+ | ⭐⭐⭐ | ⭐⭐⭐ | ⭐⭐⭐ |
性能说明：行生成器（lateral 内部）与 json_table 均为高效路线；xml 与 connect_by_root 层级变体明显更慢（第三方实测 xml 约慢 10 倍、connect_by_root 约慢 40 倍以上）。星级为相对开销，拼接列本就无法走索引。
4. 选型总结
- 12c+ 性能优先：json_table——最快，但注意 3.1 的特殊字符前提
- 12c+ 可读性优先：lateral——无转义问题、无 hack，官方同款路线，团队易维护
- 10g/11g 兼容：xmltable（记得转义）或 regexp+connect by（数据量小可接受）
- 需要复用：把逻辑封装成自定义函数（参考 2.4 的 find_in_set）
- 根本建议：数据量大、查询频繁时，应把拼接列规范化为子表，而非在 sql 里反复拆分
以上就是oracle数据库逗号拼接字段处理的详细内容，更多关于oracle逗号拼接字段的资料请关注代码网其它相关文章！
发表评论