Substring from underscore and onwards in Oracle

Question

I have a string with under score and some characters. I need to apply substring and get values to the left excluding underscore. So I applied below formula and its working correctly for those strings which have underscore (_). But for strings without (_) it is bringing NULL. Any suggestions how this can be handled in the substring itself.

Ex: ABC_BASL ---> Works correctly; ABC ---> gives null

My formula as below -

select SUBSTR('ABC_BAS',1,INSTR('ABC_BAS','_')-1) from dual;
ABC

select SUBSTR('ABC',1,INSTR('ABC','_')-1) from dual;
(NULL)

`NULL` seems correct according to your description. What do you want? — Gordon Linoff, Jun 20 '20 at 11:19

Tim Biegeleisen · Answer 1 · 2020-06-20T07:44:59.153

4

You could use a CASE expression to first check for an underscore:

WITH yourTable AS (
    SELECT 'ABC_BAS' AS col FROM dual UNION ALL
    SELECT 'ABC' FROM dual
)

SELECT
    CASE WHEN col LIKE '%\_%' ESCAPE '\'
         THEN SUBSTR(col, 1, INSTR(col, '_') - 1)
         ELSE col END AS col_out
FROM yourTable;

edited Jun 20 '20 at 07:44

answered Jun 20 '20 at 03:06

Tim Biegeleisen

502,043
27
286
360

You need to escape the wildcard underscore for `like` - [db<>fiddle](https://dbfiddle.uk/?rdbms=oracle_18&fiddle=327c07e5646e331684e6254026e38917) (and your CTE is missing `union all` *8-). – Alex Poole Jun 20 '20 at 07:25

Bob Jarvis - Слава Україні · Answer 2 · 2020-06-22T22:58:30.017

Use regular expression matching:

SELECT REGEXP_SUBSTR('ABC_BAS', '(.*)([_]|$)?', 1, 1, NULL, 1) FROM DUAL;

returns 'ABC', and

SELECT REGEXP_SUBSTR('ABC', '(.*)([_]|$)?', 1, 1, NULL, 1) FROM DUAL;

also returns 'ABC'.

db<>fiddle here

EDIT

The above gives correct results, but I missed the easiest possible regular expression to do the job:

SELECT REGEXP_SUBSTR('ABC_BAS', '[^_]*') FROM DUAL;

returns 'ABC', as does

SELECT REGEXP_SUBSTR('ABC', '[^_]*') FROM DUAL;

db<>fiddle here

score 2 · Answer 3 · answered Jun 20 '20 at 03:55

2

Yet another approach is to use the DECODE in the length parameter of the substr as follows:

substr(str, 
       1, 
       decode(instr(str,'_'), 0, lenght(str), instr(str,'_') - 1)
       )

answered Jun 20 '20 at 03:55

Popeye

35,427
4
10
31

score 1 · Answer 4 · answered Jun 20 '20 at 11:22

You seem to want everything up to the first '_'. If so, one method usesregexp_replace():

select regexp_replace(str, '(^[^_]+)_.*$', '\1')
from (select 'ABC' as str from dual union all
      select 'ABC_BAS' from dual
     ) s

A simpler method is:

select regexp_substr(str, '^[^_]+')
from (select 'ABC' as str from dual union all
      select 'ABC_BAS' from dual
     ) s

Here is a db<>fiddle.

William Robertson · Answer 5 · 2020-06-24T06:52:13.770

I'd use

regexp_replace(text,'_.*')

or if performance was a concern,

substr(text, 1, instr(text||'_', '_') -1)

For example,

with demo(text) as
   ( select column_value
     from   table(sys.dbms_debug_vc2coll('ABC', 'ABC_DEF', 'ABC_DEF_GHI')) )
select text
     , regexp_replace(text,'_.*')
     , substr(text, 1, instr(text||'_', '_') -1)
from   demo;
    
TEXT         REGEXP_REPLACE(TEXT,'_.*') SUBSTR(TEXT,1,INSTR(TEXT||'_','_')-1)
------------ --------------------------- -------------------------------------
ABC          ABC                         ABC
ABC_DEF      ABC                         ABC
ABC_DEF_GHI  ABC                         ABC

score 0 · Answer 6 · edited Jun 20 '20 at 07:29

0

Ok i think i got it. Add nvl to the substring and insert the condition as below -

select nvl(substr('ABC',1,instr('F4001Z','_')-1),'ABC') from dual;

edited Jun 20 '20 at 07:29

Alex Poole

183,384
11
179
318

answered Jun 20 '20 at 03:05

Arun.K

103
2
4
21

Substring from underscore and onwards in Oracle

6 Answers6

EDIT