Search code examples
sqloracle-databasesubstr

Oracle substr before a specific character

I am trying to obtain the string before '_' character, I am using:

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

This works fine for this case, but I want to modify it to work with values which don't have '_'.

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

In this case it will output NULL and this does not work for me!

How can I modify this query so it will output 'Lorik', but when '_' is present it will remove it and the string after it.

P.S. I cannot use Case when statements

Solution

  • One option, using REGEXP_SUBSTR:

    WITH yourTable AS (
    SELECT 'Lorik_1' AS col FROM dual UNION ALL
    SELECT 'Lorik' FROM dual
)

SELECT REGEXP_SUBSTR (col, '^[^_]+')
FROM yourTable;

    This outputs Lorik for both columns, see the demo here:

    Demo