最近同事遇到一个问题,需要把一个字段内容按照;分隔成多行,通过度娘和查资料终于解决了这个问题,下来就来絮叨一下.

刚开始查到的都是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&gt; 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 字符串固定字符 分多 的方法。