Remove a random expression from string

Question

I have a string/column something like this

String a = "000003023_AggregateStopLossLimit_W x3A 973911_2012-12-22.PDF";

I want to create a substring which doesn't have the part ' x3A 973911' in it.

Whic means I want something like this,

000003023_AggregateStopLossLimit_W_2012-12-22.PDF

There is a list of such strings which will have different values but the format will be the same. I want the part of string to be removed which comes after the first space and ends at the next '_'.

This is what I have done already, this is working fine, but want to know if there is a better way of doing it.

String b = a.replaceAll(a.substring(a.indexOf(" "), a.indexOf("_",a.indexOf(" "))),"");

It would be even better if I can do this in db itself, which is oracle, instead of in java. Any idea to get this formatted string from the column directly using select?

One more requirement, I dont want to display the extension of the file.
So nothing after the '.' should be displayed, which means something like this '000003023_AggregateStopLossLimit_W_2012-12-22'
I tried the following using the previous solution of APC

 select regexp_replace ( your_string
                          , '([^[:space]]*) (.*)_(.*)....'
                          , '\1_\3') as new_string from your_table

This is working fine for now.
This should be removing last 4 characters and have the risk of not getting proper result if the extension is more or less than 3 or if the string is not truncated.
I'm looking for a more aesthetic way to do it.
Any chance?

Answer 1

final String r = a.replaceAll(" .*?(?=_)", "");

if you print the r, it gave output:

000003023_AggregateStopLossLimit_W_2012-12-22.PDF