asked on

instr twice to substring variable length substring in oracle

HI

I have a column that has a substring following 1st "_" and before 2nd "_" that I want parsed out.

1234567890123_ABCD_AN_XX_SRCV_A

1234567890123_ABCDE_AN_XX_FARB_B

So the substring I want is ABCD and ABCDE (this can be any length)

The digits are always 13 at the start of the full string. And there are 5 "_" in the full string always.

ASKER CERTIFIED SOLUTION

Pawan Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Pawan Kumar

Output -


1      ABCD
2      ABCDE

Pawan Kumar

more dynamic -

SELECT SUBSTR('1234567890123_ABCD_AN_XX_SRCV_A',INSTR('1234567890123_ABCD_AN_XX_SRCV_A', '_', 1, 1)+1,INSTR('1234567890123_ABCD_AN_XX_SRCV_A', '_', 1, 2)
- INSTR('1234567890123_ABCD_AN_XX_SRCV_A', '_', 1, 1)-1) FROM dual

SELECT SUBSTR('1234567890123_ABCDE_AN_XX_FARB_B',INSTR('1234567890123_ABCDE_AN_XX_FARB_B', '_', 1, 1)+1,INSTR('1234567890123_ABCDE_AN_XX_FARB_B', '_', 1, 2)
- INSTR('1234567890123_ABCDE_AN_XX_FARB_B', '_', 1, 1)-1) FROM dual

Ouptut

1 ABCD
2 ABCDE

Sean Stuber

REGEXP_SUBSTR(yourstring, '[^_]+', 15)

example usage...

SELECT yourstring, REGEXP_SUBSTR(yourstring, '[^_]+', 15)
FROM (SELECT '1234567890123_ABCD_AN_XX_SRCV_A' yourstring FROM DUAL
UNION ALL
SELECT '1234567890123_ABCDE_AN_XX_FARB_B' FROM DUAL)