Remove all characters after a specific character in PL/SQL [duplicate]

Question

How do I get a substring from this example value:

 john.abc_1234

I want it to return john.abc.So basically we need to remove all the information after _.

More examples: 1234_abc

Answer 1

You can use SUBSTR and INSTR:

select substr('john.abc_1234', 1, instr('john.abc_1234', '_') -1)
from dual

Warning: This is only guaranteed to work if your string actually has an underscore in it

Update

Additionally, if you are running from Oracle 10g on, you could take the Regex path, which would more powerfully handle exceptions.

Here are some links on how to do it in Oracle:

• http://psoug.org/reference/regexp.html
• http://psoug.org/snippet/Regular-Expressions--Regexp-Cheat-Sheet_856.htm
• http://www.regular-expressions.info/oracle.html

Answer 2

You can use via a simple combination of substr(..) and instr(...) to find the string which is free from underscore special charecter. Also in order to only select _ containing strings you can make use of additional where clause as below

       select substr('john.abc_1234', 1, 
       instr('john.abc_1234', '_') -1)
      from dual where 
      instr('john.abc_1234', '_')>0