在Oracle SQL中找到一个子字符串，“在第一个_(下划线)开始”和“第二个_(UnderScore)结束”，使用R

Question

我的输入模式如：

WITH data_tab AS (
  SELECT '1540_INPUTTER' user_name FROM  dual 
  UNION SELECT '1540_RAZZ25_UNKNOWN' FROM  dual
  UNION SELECT '1540_RAKIB17_OS_WIN10' FROM  dual
  )
SELECT REGEXP_SUBSTR(user_name,…………………….....) AS st_user_name from data_tab

所需的输出：

ST_USER_NAME
------------
INPUTTER
RAZZ25
RAKIB17

Answer 1

做到的一种方法是

WITH data_tab AS (
    SELECT '1540_INPUTTER' user_name FROM  dual 
    UNION SELECT '1540_RAZZ25_UNKNOWN' FROM  dual
    UNION SELECT '1540_RAKIB17_OS_WIN10' FROM  dual
)
SELECT REGEXP_SUBSTR(user_name,'_([^_]*)', 1, 1, 'i', 1) AS st_user_name 
FROM data_tab;

Answer 2

另一种方法是定义字符串的完整结构并提取第二组：

WITH data_tab AS (
  SELECT '1540_INPUTTER' user_name FROM  dual
  UNION SELECT '1540_RAZZ25_UNKNOWN' FROM  dual
  UNION SELECT '1540_RAKIB17_OS_WIN10' FROM  dual
  )
SELECT REGEXP_SUBSTR(user_name,'(d{4}_)([A-Z0-9]+)(_)?(w+)?',1,1,'i',2)

作为st_user_name 来自data_tab;

Answer 3

检查此。

WITH data_tab AS (
  SELECT '1540_INPUTTER' user_name FROM  dual 
  UNION SELECT '1540_RAZZ25_UNKNOWN' FROM  dual
  UNION SELECT '1540_RAKIB17_OS_WIN10' FROM  dual
  )
SELECT
case when INSTR(SUBSTR(user_name, INSTR(user_name, '_')+1, length(user_name)- INSTR(user_name, '_')+1 ),'_') =0 then
SUBSTR(user_name, INSTR(user_name, '_')+1, length(user_name)- INSTR(user_name, '_')+1 )
else
substr((SUBSTR(user_name, INSTR(user_name, '_')+1, length(user_name)- INSTR(user_name, '_')+1 )), 1, INSTR(SUBSTR(user_name, INSTR(user_name, '_')+1, length(user_name)- INSTR(user_name, '_')+1 ),'_') -1)
end as user_name
from data_tab