How to Select a substring in Oracle SQL up to a specific character?

Using a combination of SUBSTR, INSTR, and NVL (for strings without an underscore) will return what you want:

SELECT NVL(SUBSTR('ABC_blah', 0, INSTR('ABC_blah', '_')-1), 'ABC_blah') AS output  FROM DUAL

Result:

output------ABC

Use:

SELECT NVL(SUBSTR(t.column, 0, INSTR(t.column, '_')-1), t.column) AS output  FROM YOUR_TABLE t

Reference:

• SUBSTR
• INSTR

Addendum

If using Oracle10g+, you can use regex via REGEXP_SUBSTR.

This can be done using REGEXP_SUBSTR easily.

Please use

REGEXP_SUBSTR('STRING_EXAMPLE','[^_]+',1,1) 

where STRING_EXAMPLE is your string.

Try:

SELECT REGEXP_SUBSTR('STRING_EXAMPLE','[^_]+',1,1) from dual

It will solve your problem.

You need to get the position of the first underscore (using INSTR) and then get the part of the string from 1st charecter to (pos-1) using substr.

  1  select 'ABC_blahblahblah' test_string,  2         instr('ABC_blahblahblah','_',1,1) position_underscore,  3         substr('ABC_blahblahblah',1,instr('ABC_blahblahblah','_',1,1)-1) result  4*   from dualSQL> /TEST_STRING      POSITION_UNDERSCORE RES---------------- ------------------  ---ABC_blahblahblah                  4  ABC

Instr documentation

Susbtr Documentation