最近同事遇到一个问题,需要把一个字段内容按照;分隔成多行,通过度娘和查资料终于解决了这个问题,下来就来絮叨一下.
刚开始查到的都是replace、substr和instr连用
replace语法:
replace(字符串,要被替换的字符,替换后的字符)
substr语法:
substr函数格式 (俗称:字符截取函数)
格式1: substr(string string, int a, int b);
格式2:substr(string string, int a) ;
格式1:
1、string 需要截取的字符串
2、a 截取字符串的开始位置(注:当a等于0或1时,都是从第一位开始截取)
3、b 要截取的字符串的长度
格式2:
1、string 需要截取的字符串
2、a 可以理解为从第a个字符开始截取后面所有的字符串。
instr语法:
instr()函数的格式 (俗称:字符查找函数)
格式一:instr( string1, string2 ) // instr(源字符串, 目标字符串)
格式二:instr( string1, string2 [, start_position [, nth_appearance ] ] ) // instr(源字符串, 目标字符串, 起始位置, 匹配序号)
解析:string2 的值要在string1中查找,是从start_position给出的数值(即:位置)开始在string1检索,检索第nth_appearance(几)次出现string2位置下标。
注:在Oracle/PLSQL中,instr函数返回要截取的字符串在源字符串中的位置。只检索一次,也就是说从字符的开始到字符的结尾就结束。
数据就是下面这种情况:
首先看一下这三个联合函数可以实现的效果:
要想多行显示只能使用union all来实现(因为开头还有一个;,所以直接就从第二个;开始,这样是不易用的假如之后有三个字符串的时候他就失效了)
select noid,replace(substr(str,instr(str,';')+1,instr(str,';',2)-1),';') from test
union all
select noid,replace((substr(str,instr(str,';')+1,instr(str,';',3)-1)),';') from test
从这个结果可以看出来,假如字段只有一个值的时候,分隔出的第二个字符串就是null,所以这种办法是行不通的;
后面又查到一个方法就是通过REGEXP_SUBSTR搭配伪列来实现
REGEXP_SUBSTR语法:
这个函数的作用是正则分隔字符串,用法为
function REGEXP_SUBSTR(String, pattern, position, occurrence, modifier)
String:需要进行正则处理的字符串
pattern:进行匹配的正则表达式
position:起始位置,从第几个字符开始正则表达式匹配(默认为1)
occurrence:标识第几个匹配组,默认为1
modifier:模式('i'不区分大小写进行检索;'c'区分大小写进行检索。默认为'c'。)
伪列的话使用rownum或level搭配CONNECT BY来使用(它可以实现连续的值):
SELECT REGEXP_SUBSTR(';1010001830;1010001831', '[^;]+', 1, rownum)
FROM dual connect BY rownum <= LENGTH(';1010001830;1010001831') - LENGTH(regexp_replace(';1010001830;1010001831', ';', ''));
[^;]+:不包含;的任意字符,字符个数不限,但至少一个字符
向上面这样的一条数据是没问题的,但是一旦换成字段的话,他只能分隔出最长的一个字符串,剩下的都是null值(这个有点匪夷所思);
SELECT REGEXP_SUBSTR(str, '[^;]+', 1, rownum)
FROM test connect BY rownum <= LENGTH(str) - LENGTH(regexp_replace(str, ';', ''));
最后查了好多资料终于找到了正确的方法:
select t1.noid, regexp_substr(t1.str, '[^;]+', 1, level) as hsid
from test t1
connect by t1.noid = prior t1.noid
and prior dbms_random.value is not null
and level <= REGEXP_COUNT(str, '[^;]+')
这样就是最完美的sql,下面简单看看这个sql:
REGEXP_COUNT 等价于LENGTH(str) - LENGTH(regexp_replace(str, ';', ''))
REGEXP_COUNT语法:
Oracle的11g引入此函数
REGEXP_COUNT ( source_char, pattern [, position [, match_param]])
REGEXP_COUNT 返回pattern 在source_char 串中出现的次数。如果未找到匹配,则函数返回0。position 变量告诉Oracle 在源串的什么位置开始搜索。在开始位置之后每出现一次模式,都会使计数结果增加1。
match_param 变量支持下面几个值:
‘i’ 用于不区分大小写的匹配
‘c’ 用于区分大小写的匹配
‘n’ 允许句点(.)作为通配符去匹配换行符。如果省略该参数,则句点将不匹配换行符
‘m’ 将源串视为多行。即Oracle 将^和$分别看作源串中任意位置任何行的开始和结束,而不是仅仅看作整个源串的开始或结束。如果省略该参数,则Oracle将源串看作一行。
‘x’ 忽略空格字符。默认情况下,空格字符与自身相匹配。
connect by t1.noid = prior t1.noid
and prior dbms_random.value is not null
这个可以理解为是循环;
最近同事遇到一个问题,需要把一个字段内容按照;分隔成多行,通过度娘和查资料终于解决了这个问题,下来就来絮叨一下.刚开始查到的都是replace、substr和instr连用replace语法:replace(字符串,要被替换的字符,替换后的字符)substr语法:substr函数格式 (俗称:字符截取函数)格式1: substr(string string, int a, int b);格式2:substr(string string, int a) ;解析: ...
SELECT DISTINCT T.NAME,
REPLACE(REGEXP_SUBSTR(T.TESTSTR, '[^,]+', 1, LEVEL), ',', ' ') TESTSTR
FROM TEST2 T
WHERE 1 = 1
CONNECT BY LEVEL <= (LENGTH(T.TESTSTR) - LENGTH(REPLACE(T.TESTSTR, ',', '')) + 1);
-- Create table TEST2
create
在数据存储数据库中,常常存在信息以特殊符号隔开形式存储在一个
字段
里,如下表:当我们需要把“去过的城市”
字段
以逗号(,)
拆
分成多
行
时,如下表:这时就会涉及到列转
行
,下面跟着我的例子,一起来见证奇迹吧:
插入测试数据:
查询sql
将代码拷贝使用时,需要更改表名、
字段
名、
分隔
字符等(注:使用的时候还需将将N替换为
分隔
符的长度,如分割符“、”,长度位1,则将N替换位1,一共4处)。
执
行
结果
可以从上截图看出,已经将userinfomation表的city
字段
根据“、”号分割后,形成多
行
数据。赶快去试试吧。
select regexp_substr('444.555.666', '[^.]+', 1, level) col
from dual
connect by level <= regexp_count('444.555.666', '\.') + 1
输出结果:
多
行
拆
分
如果数据表存在多
行
数据需要
拆
分,也可以在原表上使用connect+正则的方法:
最初这个
拆
分的语句我也是从别人的博客搜来的,但是基本上只能根据单个
分隔
字符(即
分隔
字符的长度为1)来
拆
分。经过我反复测试,修改了好一些代码,终于成功进
行
了拓展,
分隔
字符长度为2、3甚至更多时一样能够成功
拆
分。所以这篇文章算是原创也说得过去吧?
1.根据单个字符进
行
拆
分
– 创建表
create table myfriendsTest(
friendid number(18),
ORACLE
中将带
分隔
符的字符串
拆
分成多
行
,有很多方法,我将多种常见和不常见的
拆
分方法进
行
了收集整理。 通常这个操作被归类为
行
列转换的范畴。
为了方便测试,我将每一种方法封装成一个函数,返回一个字符串集合。
0.创建自定义集合类型
SQL> create type t_vchars as table of varchar2(4000);
Type created
在
Oracle
中,我们可以使用SUBSTR函数来
拆
分字符串为多
行
显示。以下是详细的步骤:
1. 首先,我们需要创建一个表来存储我们要
拆
分的字符串。假设我们创建了一个名为my_table的表,其中包含一个名为my_string的
字段
用来存储字符串。
2. 接下来,我们可以使用INSERT语句将要
拆
分的字符串插入到my_table表中的my_string
字段
中。
3. 然后,我们可以使用以下SQL语句来
拆
分字符串并将其显示为多
行
:
SELECT SUBSTR(my_string, 1, 10) AS line1,
SUBSTR(my_string, 11, 10) AS line2,
SUBSTR(my_string, 21, 10) AS line3
FROM my_table;
在上面的示例中,我们将字符串按照每
行
最大10个字符的长度进
行
拆
分。你可以根据实际需求和字符串长度进
行
调整。
4. 最后,我们可以执
行
以上SQL语句来获取
拆
分后的结果,每一
行
将展示一个固定长度的子字符串。
以上是使用
Oracle
进
行
字符串固定字符
拆
分多
行
的方法。