connect by常常结合prior一起实现父级查询。因此connect by LEVEL prior一般都一起出现。 附 最后再次附上针对原始的业务需求的完整的语句及输出: select regexp_substr(tech_class, '[^,]+', 1, LEVEL) as class_name, tech_name from teacher CONNECT BY LEVEL <= REGEXP_COUNT(tech_class, '[^,]+')...
REGEXP_SUBSTR 结合 CONECT BY LEVEL 代码语言:sql 复制 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’,...
SELECT LEVEL FROM DUAL CONNECT BY LEVEL <=7; 结果: LEVEL --- 1 2 3 4 5 6 7 4、将上面REGEXP_SUBSTR的occurrence关联 [sql] SELECT NVL(REGEXP_SUBSTR('17,20,23', '[^,]+', 1, LEVEL, 'i'), 'NULL') AS STR FROM DUAL CONNECT BY LEVEL <= 7; 结果: STR --- 17 20 23 NULL...
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.com --- 17 ...
1. regexp_substr函数只能返回字符串的一个子串,如果需要提取多个子串,需要使用CONNECT BY和LEVEL来实现。 2. 如果源字符串中没有匹配到指定的模式,regexp_substr函数将返回NULL。 3. 在使用正则表达式时,需要根据实际情况正确设置pattern和match_param参数,以确保匹配的准确性。 五、总结 本文介绍了Oracle中regexp...
如果你想将一个由分号分隔的字符串分割为多行,你可以使用REGEXP_SUBSTR结合TABLE函数和CAST: 代码语言:javascript 复制 SELECTREGEXP_SUBSTR('A;B;C;D','[^;]+',1,LEVEL)ASitemFROMdualCONNECTBYLEVEL<=REGEXP_COUNT('A;B;C;D','[^;]')+1; ...
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.com --- 17 ...
SELECT REGEXP_SUBSTR('17,20,23','[^,]+',1,3,'i') AS STR FROM DUAL; 结果: STR --- 23 3、获取一个多个数值的列,从而能够让结果以多行的形式展示出来 [sql] SELECT LEVEL FROM DUAL CONNECT BY LEVEL <=7; 结果: LEVEL --- 1 ...
SELECT regexp_substr('1a2b3c', '[[:alnum:]]+', 1, level) FROM dual CONNECT BY regexp_substr('1a2b3c', '[[:alnum:]]+', 1, level) IS NOT NULL; 该语句会返回以下结果: 1 a 2 b 3 c 接下来,我将使用该函数来生成一个长度为700字左右,由列表划分的中文文字示例: 在很多时候,我们需...