代码语言:sql 复制 SELECTregexp_substr(temp.STUDENT,'[^,]+',1,LEVEL)ASSTUDENT,regexp_substr(temp.SUBJECT,'[^,]+',1,LEVEL)ASSUBJECT,regexp_substr(temp.SCORT,'[^,]+',1,LEVEL)ASSCORTFROM(SELECT'Student001,Student001,Student002,Student002,Student003'ASSTUDENT,'CHINESE,ENGLISH,CHINESE,MATH,...
connectbyrownum<=length(regexp_replace(id,'[^,]+'))+1;/*结果: 2*/ 2、获取差集(差异值): selectregexp_substr(id,'[^,]+',1, rownum) idfrom(select'1,2,3,5'idfromdual) connectbyrownum<=length(regexp_replace(id,'[^,]+'))+1minus--取差集selectregexp_substr(id,'[^,]+',1,...
connectbyrownum<=length(regexp_replace(id,'[^,]+'))+1;/*结果: 2*/ 2、获取差集(差异值): selectregexp_substr(id,'[^,]+',1, rownum) idfrom(select'1,2,3,5'idfromdual) connectbyrownum<=length(regexp_replace(id,'[^,]+'))+1minus--取差集selectregexp_substr(id,'[^,]+',1,...
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...
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; 结果: www.2cto.com LEVEL...
connect by Level<=LENGTH(q.nums)-LENGTHREGEXP_REPLACEqnums+1order by order_num 业务:把nums按逗号拆分为多行。 REGEXP_SUBSTR函数格式如下: function REGEXP_SUBSTR(String, pattern, position, occurrence, modifier) __srcstr :需要进行正则处理的字符串 ...
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...
SELECT LEVEL FROM DUAL CONNECT BY LEVEL <=7; 结果: --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
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...
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 ...