SQL进阶:字段中包含CSV,如何通过Sql解析CSV成多行多列?

一、问题描述

Oracle数据库中某个字段value是CLOB类型,存的是csv格式的数据,如下所示

classno	value
1	name,age,sex,… ‘李世民’,20,‘M’,…’ ‘李治’,18,‘M’,… ‘武则天’,16,‘F’,… ‘李隆基’,14,‘M’,…
2	…

需要把上述clob类型的csv字段用Sql的方式展开,如上述csv字段有四行三列,就需要把上述字段转成实际的四行三列,如下所示

classno	name	age	sex	…
1	李世民	20	M
1	李治	18	M
1	武则天	16	F
1	李隆基	14	M
2	…	…	…

二、解决思路

<一>、拆成多行

• 按照换行符拆分一个个的列表,上述换行符是\n,按照\n进行拆分比较难写,考虑先把\n替换成其它符号,如分号

换行符在oracle中用chr(10)表示
select replace(value,chr(10),';') as value from table

• 按照换行符进行拆分字符串

select to_char(regexp_substr(value,'[^;]+',1,level) as split_value
  from table
connect by level <= regexp_count(value,'[^;]+',1)
    and prior class_no = class_no
    and prior sys_guid() is not null

regexp_substr()函数为拆分字符串,若没有connect by语句,只是

select to_char(regexp_substr(value'[^;]+',1) as split_value
  from table

则不会循环进行拆分,只会拆分第一段,比如我那个例子,只会获取到

classno	value
1	‘李世民’,20,‘M’
2	…

CONNECT BY是Oracle SQL中的一个子句，用于定义层次结构或递归关系，从而进行层次结构数据的查询。
LEVEL是Oracle SQL中的一个伪列，用于在层次结构或递归查询中获取当前行的级别。
REGEXP_COUNT 用于计算字符串中正则表达式匹配的次数

上述level <= regexp_count(value,‘[^;]+’,1)就是递归停止的条件

prior条件指的是当前递归在哪个层级下运行,比如上述例子一个csv字段描述的是一个班级的事情,递归是在这个班级下运行,所以prior条件要加上prior class_no = classno,不然会造成数据重复
需要注意prior后接的条件需要能够限制某个递归层级,不然可能会造成数据不断的循环
若是有多个prior条件,可以
and prior col1 = col1
and prior col2 = col2
而不是
and prior col1 = col1 and col2 = col2

经过上述处理之后,得到的结果应该是

classno	value
1	name,age,sex,…
1	‘李世民’,20,‘M’,…
1	’ ‘李治’,18,‘M’,…
1	‘武则天’,16,‘F’,…
1	‘李隆基’,14,‘M’,
2	…

已经拆成多行了,剩下的是拆成多列

<二>、拆成多列

• 根据列的分隔符来拆分,以逗号为例

select regexp_substr(split_value,'[^,]+',1,1) as name,
       regexp_substr(split_value,'[^,]+',1,2) as age,
       regexp_substr(split_value,'[^,]+',1,3) as sex
   from table

• 还是用regexp_substr函数来拆分,只不过不进行递归查询,

三、代码实现

with tmp as (
	select classno,replace(value,chr(10),';') as value
	  from table
),tmp1 as (
	select to_char(regexp_substr(value,'[^;]+',1,level)) as split_value,classno
	  from tmp
   connect by level <= regexp_count(value,'[^;]+',1)
	   and prior classno = classno
	   and prior sys_guid() is not null
),tmp2 as (
	select classno,
	       regexp_substr(value,'[^,]+',1,1) as name,
	       regexp_substr(value,'[^,]+',1,2) as age,
	       regexp_substr(value,'[^,]+',1,3) as sex
	  from tmp1
)
	select classno,
	       name,
	       age,
	       sex
	  from tmp2 
	 where name != 'name'