Oracle-分割字符串前字符后字符sql
regexp_instr:函数它返回一个整数,指示开始或结束匹配的子位置,这取决于return_option参数的值。如果没有找到匹配,则函数返回0。
例如:
M1 | M2 |
---|---|
7212205/7211914 | 20210806 |
目标形式
M1 | M2 |
---|---|
7212205 | 20210806 |
7211914 | 20210806 |
参考sql:
select count(*) from (
select distinct MAT_BATCH_NO,max(END_PROD_TIME) from (
select regexp_instr(MAT_BATCH_NO, '/', 1, 1) as pos,
substr(MAT_BATCH_NO, 1-regexp_instr(MAT_BATCH_NO, '/', 1, 1)) as MAT_BATCH_NO,END_PROD_TIME
from (select distinct MAT_BATCH_NO,substr(END_PROD_TIME,0,8) AS END_PROD_TIME from TMMSM21 T21 where MAT_BATCH_NO <> ' ')
union all
select regexp_instr(MAT_BATCH_NO, '/', 1, 1) as pos,
substr(MAT_BATCH_NO, 0, regexp_instr(MAT_BATCH_NO, '/', 1, 1)-1) as MAT_BATCH_NO,END_PROD_TIME
from (select distinct MAT_BATCH_NO,substr(END_PROD_TIME,0,8) AS END_PROD_TIME from TMMSM21 T21 where MAT_BATCH_NO <> ' ')
)
WHERE 1 = 1
group by MAT_BATCH_NO
)