oracle pl/sql level妙用

本文介绍了如何使用Oracle数据库中的Level关键字及正则表达式函数来将包含特定分隔符的字符串拆分成多列数据。首先通过Level关键字实现简单字符串拆分,接着针对不规则字符串介绍使用正则表达式的解决方案,并提供了另一种不依赖正则表达式的拆分方法。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

1、当需要把一个字符串按某一分隔符分隔后,变为数据列,即把字符串行变为列,可以使用level关键字,例子:

 

with t as
 (select 'a;b;c;d;e' as str from dual)
select level,
	   t.str,
	   substr(t.str, 2 * (level - 1) + 1, 1) as str_signle
  from t
connect by level <= length(t.str) - length(replace(t.str, ';', '')) + 1;

 

运行结果:

 

2、上面的写法只是适用于一般有规律的字符串行,当遇到不规则字符串行时,可以使用oracle的正则表达式函数,请看下面的例子:

 

with t as
 (select 'i;am;a;test;hahahhah' as str from dual)
select level,
	   str,
	   regexp_substr(t.str, '[^;]+', 1, level) str_single
  from t
connect by level <= length(t.str) - length(replace(t.str, ';', '')) + 1;

 

运行结果:

或者不使用正则表达式:

 

with t_org as
 (select 'I am a complicated string' as str from dual),
t_sep as
 (select ' ' as sep from dual),
t as
 (select t_org.str as orign_str,
		 t_sep.sep || t_org.str || t_sep.sep as str
	from t_org,
		 t_sep)
select level,
	   t.orign_str,
/*	   instr(t.str, t_sep.sep, 1, level) as separator_postion,
	   instr(t.str, t_sep.sep, 1, level) + 1 as str_postion_begin,
	   instr(t.str, t_sep.sep, 1, level + 1) -
	   instr(t.str, t_sep.sep, 1, level) - 1 as str_single_len,*/
	   substr(t.str, instr(t.str, t_sep.sep, 1, level) + 1, instr(t.str, t_sep.sep, 1, level + 1) -
			   instr(t.str, t_sep.sep, 1, level) - 1) as str_signle
  from t,
	   t_sep
connect by level < length(t.str) - length(replace(t.str, t_sep.sep, ''));

 

运行结果:


 

评论 4
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值