Remove all characters after a specific character in PL/SQL

sql oracle plsql oracle9i substr
sandy picture sandy · Oct 10, 2011 · Viewed 56k times · Source

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

Adriano Carneiro picture Adriano Carneiro · Oct 10, 2011

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:

Related questions

Single SQL SELECT Returning multiple rows from one table row

We have a table which is of the form: ID,Value1,Value2,Value3 1,2,3,4 We need to transform this into. ID,Name,Value 1,'Value1',2 1,'Value2',3 1,'Value3',4 Is there a clever way of doing this in one SELECT statement (…

Read More
Pattern Matching with Like Clause

I am attempting to use a LIKE clause in a SQL statement to match a certain pattern within Oracle. I would like to do something as such: LIKE '[A-Z][A-Z][1-4]%' ..but I can't use a regex because …

Read More
count number of rows that occur for each date in column date range

I have a table with data such as below Group Start Date End Date A 01/01/01 01/03/01 A 01/01/01 01/02/01 A 01/03/01 01/04/01 B 01/01/01 01/01/01 ETC I am looking to produce a view that gives a count for each day, like this Group Date Count A 01/01/01 2 A 01/02/01 2 …

Read More