REGEXP_SUBSTR 结合 CONECT BY LEVEL 代码语言:sql AI代码解释 SELECTregexp_substr(temp.STUDENT,'[^,]+',1,level)STUDENTFROM(SELECT'Student001,Student002,Student003'STUDENTFROMdual)tempCONNECTBYlevel<=REGEXP_COUNT('Student001,Student002,Student003','[^,]+');--Student001--Student002--Student003 ...
需要返回多行,可以采用oracle的connect by 命令,行数的获得通过length来获得。 select regexp_substr(‘2001,2002,200103,2005’, ‘[^,]+’, 1, level, ‘i’) as str from dual connect by level <= length(‘2001,2002,200103,2005’) - length(regexp_replace(‘2001,2002,200103,2005’, ‘,’,...
2)在oracle中rownum和level都是自动生成数字序列集合,结合connect by使用 selectDISTINCT regexp_substr('A,B,C,D,E,F','[^,]+',1,level)fromDUAL connect by level<= length('A,B,C,D,E,F') - length(replace('A,B,C,D,E,F',',','')) +1;selectDISTINCT regexp_substr('A,B,C,D,E,...
SELECT LEVEL FROM DUAL CONNECT BY LEVEL <=7; 结果: www.2cto.com LEVEL --- 1 2 3 4 5 6 7 4、将上面REGEXP_SUBSTR的occurrence关联 [sql] SELECT NVL(REGEXP_SUBSTR('17,20,23', '[^,]+', 1, LEVEL, 'i'), 'NULLL') AS STR FROM DUAL CONNECT BY LEVEL <= 7; STR www.2cto.c...
使用CONNECT BY和LEVEL伪列 使用PL/SQL 存储过程 下面是每种方法的详细说明和示例。 方法1:使用REGEXP_SUBSTR函数 REGEXP_SUBSTR函数可以用来提取字符串中符合正则表达式的子串。假设我们有一个表my_table,其中有一列my_column,我们想将其基于逗号拆分为三列。
regexp_substr(DESCR, '[^/]+', 1, level) value from RFDGENERIC where id = 14966150 connect by level <= length(DESCR) - length(replace(DESCR, '/')) + 1 and prior DESCR = DESCR and prior sys_guid() is not null; 但结果是。
SELECTREGEXP_SUBSTR('123,,,ABaC,!@#,,,','[^a]+',1,2,'c')FROMDUAL ; AI代码助手复制代码 区分大小写分割后的第二个值 结果:C,!@#,,, SELECTREGEXP_SUBSTR('123,,,ABC,!@#,,,','[^,]+',1, LEVEL)FROMDUALCONNECTBYREGEXP_SUBSTR('123,,,ABC,!@#,,,','[^,]+',1, LEVEL)IS...
在Oracle中,可以使用CONNECT BY LEVEL语句和REGEXP_SUBSTR函数来实现根据逗号将一行数据拆分成多行数据。以下是一个示例:假设有一个包含逗号分隔的字符串的表,例如:...
select distinct regexp_substr('020101:1:13,020101:1:175,020301:1:107+020401:1:140-020501:1:107,020301:1:107*020401:1:140/020501:1:107','[^,|+|*|/|-]+', 1, level) as str from dual connect by level <= regexp_count('020101:1:13,020101:1:175,020301:1:107+020401...
SELECTREGEXP_SUBSTR(string, '[^特定字符]+', 1, LEVEL, 'i')as分割后结果 FROMDUAL CONNECTBYLEVEL<=特定字符在字符串当中出现的次数+1; 1. 2. 3. 4. 5. 6. 7. 进一步优化 SELECTREGEXP_SUBSTR(string, '[^特定字符]+', 1, LEVEL, ...