Find a substring in Oracle SQL “after first _ (underscore) to start” and “second _ (underscore) to end” using REGEXP_SUBSTR or SUBSTR function

Question

My Input pattern like:

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

Desired Output:

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

Pham X. Bach · Accepted Answer · 2017-03-07T10:11:43.203

1

One way to do that is

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;

edited Mar 07 '17 at 10:11

answered Mar 07 '17 at 06:55

Pham X. Bach

5,284
4
28
42

Good one ..answer. – Mr. Bhosale Mar 07 '17 at 07:17

score 1 · Answer 2 · answered Mar 07 '17 at 07:01

1

Another way to do it is to define the complete structure of the string and extract the second group:

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)

AS st_user_name FROM data_tab;

answered Mar 07 '17 at 07:01

pablomatico

2,222
20
25

Do not assume facts not included in the requirement, just because you see them in the data. The OP said nothing about the first part, up to the first underscore, being exactly four digits. At the very least, before you do something like this ask the OP to confirm. – Mar 07 '17 at 16:15

score 0 · Answer 3 · answered Mar 07 '17 at 07:17

Check This.

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

Find a substring in Oracle SQL “after first _ (underscore) to start” and “second _ (underscore) to end” using REGEXP_SUBSTR or SUBSTR function

3 Answers3