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 :

ABC_DEF
XY_Z
PQRS

Expected result is

ABC
XY
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

shinobi92 · Accepted Answer · 2014-02-04 07:52:05Z

1

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;

edited Feb 4, 2014 at 7:52

shinobi92

1272 silver badges17 bronze badges

answered Feb 4, 2014 at 6:52

Maheswaran Ravisankar

17.9k6 gold badges50 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dmitry Nikiforov · Accepted Answer · 2014-02-04 08:19:31Z

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$#

Collectives™ on Stack Overflow

substring Oracle sql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related