Extracting Specific String

Assuming that is three rows of data try this:
select regexp_substr(mycol,'[0-9.]+') from mytable;

If that is one row in a table, try this:

select regexp_substr(mycol,'/([0-9.]+)',1,level,null,1) from mytable
connect by level <= regexp_count(mycol,'/[^/]+/');

If the pattern you are looking for is a series of 4 digits separated by a period

then try this

select regexp_substr(yourstring,'([0-9]\.){3}[0-9]',1,level) from yourtable
connect by regexp_substr(yourstring,'([0-9]\.){3}[0-9]',1,level)  is not null

Select allOpen in new window

thanks slightwv and sdstuber.  

The pattern that i am looking for is first 4 digit after the first back slash.  from this example diamond CU/2.2.1.0 example, i need to get 2.2.1.0

Any of the posted code should do that.

To be 100% sure:  We still need to know if your sample data is 3 rows in the table or 1?

sorry... i did not answer that question.

my data is 3 rows.

Thanks.

Then try my fist post instead of the second.  My first post will fail if the credit union name can have a number in it.

Like this:
diamond 1 CU/2.2.1.0 BatchID/com.diamond.banking.0000 BatchPlatformGroup

If that is a possibility, use sdstuber's.  Just remove the recursion.

select regexp_substr(yourstring,'([0-9]\.){3}[0-9]') from yourtable


Now that will fail if you can have data like:
diamond 1.1.1.1 CU/2.2.1.0 BatchID/com.diamond.banking.0000 BatchPlatformGroup

If that is a possibility and you really need it after the slash, let us know.

i cannot have data like this diamond 1.1.1.1 CU/2.2.1.0 BatchID/com.diamond.banking.0000 BatchPlatformGroup

But, i can have data like
diamond 1 CU/2.2.1.0 BatchID/com.diamond.banking.0000 BatchPlatformGroup

or
diamond 1 Credit Union/2.2.1.0 BatchID/com.diamond.banking.0000 BatchPlatformGroup

OK then, go with my altered version of what sdstuber posted.

Thank you slightwv nd sdstuber.  The solution provided by sdstuber worked like a charm