oracle截取字符串左右两边的字符数据，并用于排序

截取一个字符串中-最后出现的左右两边的数据

--截取左边

SELECT reverse(substr(reverse(OBJBELONG),INSTR(reverse(OBJBELONG),'-') + 1)) FROM WF_TASK;

 

--截取右边

SELECT reverse(substr(reverse(OBJBELONG),1,INSTR(reverse(OBJBELONG),'-') - 1)) FROM WF_TASK;

 

--以此来排序

order by reverse(substr(reverse(OBJBELONG),INSTR(reverse(OBJBELONG),'-') + 1)) asc,TO_NUMBER(reverse(substr(reverse(OBJBELONG),1,INSTR(reverse(OBJBELONG),'-') - 1))) asc 

 

--匹配正则查询

SELECT
*
FROM
arv_basinfo
WHERE
1 = 1
AND reverse ( substr( reverse ( arvno ), INSTR( reverse ( arvno ), '-' ) + 0 ) ) = 'CSI-ZBZD-JY-2020-'
AND TO_NUMBER(
regexp_replace(
reverse ( substr( reverse ( arvno ), 1, INSTR( reverse ( arvno ), '-' ) - 1 ) ),
'[^0-9]',
''
)
) >= 1
AND TO_NUMBER(
regexp_replace(
reverse ( substr( reverse ( arvno ), 1, INSTR( reverse ( arvno ), '-' ) - 1 ) ),
'[^0-9]',
''
)
) <= 39500
ORDER BY
TO_NUMBER(
regexp_replace(
reverse ( substr( reverse ( arvno ), 1, INSTR( reverse ( arvno ), '-' ) - 1 ) ),
'[^0-9]',
''
)
) DESC