substring Oracle sql

Question

I need to get substring of String in oralce till a character is found. if Character is not found it should show entire string

Ex :

1. ABC_DEF
2. XY_Z
3. PQRS

Expected result is

1. ABC
2. XY
3. PQRS

I tried below query But it will not work in case search charcter "_" is not found.

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

Answer 1

You can use also regular expressions (something like):

SQL> with t as (
  2  select 'ABC_DEF' x from dual union all
  3  select 'XY_Z' from dual union all
  4  select 'PQRS' from dual union all
  5  select '_MJU' from dual union all
  6  select 'POI_' from dual union all
  7  select 'PAS_PIN_APP' from dual union all
  8  select 'LIE$#' from dual
  9  )
 10  select regexp_substr(x, '[^_]*') from t
 11  /

REGEXP_SUBSTR(X,'[^_]*')                                                        
--------------------------------------------                                    
ABC                                                                             
XY                                                                              
PQRS                                                                            

POI                                                                             
PAS           

LIE$#

Answer 2

If INSTR() didn't find the character, it would return zero. So, we use DECODE to flip it to length of string itself. otherwise use the position that INSTR returns.

SELECT SUBSTR('ABC_X',0,
               DECODE(INSTR('ABC_X', '_'),
                      0,LENGTH('ABC_X'),
                      INSTR('ABC_X', '_')-1)) AS output
FROM DUAL;