我在一个表中有一个clob列，其数据如下所示
Name: ABC Place: XYZ Age: 123 Role: Developer Skill: SQL
表emp中有数百条记录，其中每个emp\u id都有一列，其描述如上所述。现在我想把这些数据显示为emp id的每一列 select emp_id, name, place, age, role, skill from emp where emp_id=xx; 使用reg exp可以这样做吗？或者其他方式？请建议。
select emp_id, name, place, age, role, skill from emp where emp_id=xx;
pieyvz9o1#
是的，可以在clob列上使用regexp\u substr：
OPS$ORACLE@FTEX>desc emp; Name Null? Type ----------------------------------------------------------------- -------- -------------------------------------------- EMP_ID NUMBER X CLOB OPS$ORACLE@FTEX>select * from emp; EMP_ID X ---------- -------------------------------------------------------------------------------- 1 Name: ABC Place: XYZ Age: 123 Role: Developer Skill: SQL OPS$ORACLE@FTEX>create or replace view v_emp as 2 ( 3 select emp_id, 4 regexp_substr(x, '(\S*)(\:)(\s)(\S*)', 1, 1, null, 4) as Name, 5 regexp_substr(x, '(\S*)(\:)(\s)(\S*)', 1, 2, null, 4) as Place, 6 regexp_substr(x, '(\S*)(\:)(\s)(\S*)', 1, 3, null, 4) as Age, 7 regexp_substr(x, '(\S*)(\:)(\s)(\S*)', 1, 4, null, 4) as Role, 8 regexp_substr(x, '(\S*)(\:)(\s)(\S*)', 1, 5, null, 4) as Skill 9 from emp 10 ); View created. OPS$ORACLE@FTEX>select * from v_emp; EMP_ID NAME PLACE AGE ROLE SKILL ---------- -------------------- -------------------- ----- -------------------- -------------------- 1 ABC XYZ 123 Developer SQL OPS$ORACLE@FTEX>
1条答案
按热度按时间pieyvz9o1#
是的，可以在clob列上使用regexp\u substr：