SQL & PL/SQL

I don't have 10g but I think this will do it:

select regexp_substr('Fast10999/2/3/4','([0-9]+)/')
from dual;

select replace(regexp_substr('Fast10999/2/3/4','[0-9]+/'),'/','') from dual

Hello

And thank you for your reply but it doesn't work, still returns 10999/. I would like to end up with 10999.

Thank you,

Hello

And thank you for your reply but I would like to solve this with regexp exclusively.

Thank you,

Ok grabbed XE

Just dropping the slash from your pattern does the trick:

select regexp_substr('Fast10999/2/3/4','[0-9]+') from dual;

and adding some of the optional parameters will get the other numbers:

select regexp_substr('Fast10999/2/3/4','[0-9]+',1,2) from dual;
select regexp_substr('Fast10999/2/3/4','[0-9]+',1,3) from dual;
select regexp_substr('Fast10999/2/3/4','[0-9]+',1,4) from dual;

Yes,

But then

select regexp_substr('Fa234st10999/2/3/4','[0-9]+')
from dual;

would return 234 and I want the number before the first slash...

Thank you,

On 10gR2,
we can use ".*?".

SQL> col after for a20

SQL> with t as (
SQL> select 'Fa234st10999/2/3/4' s from dual union all
SQL> select 'Fa234st/2/3/4' from dual union all
SQL> select '23499/2/3/4' from dual union all
SQL> select 'abcdefg' from dual union all
SQL> select '1234567' from dual)
SQL> select s as before,
SQL> RegExp_Replace(s,'^.*?([0-9]+)/.*$','\1') as after
SQL> from t;

BEFORE              AFTER
------------------  -------
Fa234st10999/2/3/4  10999
Fa234st/2/3/4       2
23499/2/3/4         23499
abcdefg             abcdefg
1234567             1234567

Oracle11g have not supported "lookahead assertion" yet.
If Oracle support "lookahead assertion",
we can use "[0-9]+(?=/)"

SQL> set null '<null>'
SQL> with t as (
  2  select 'Fa234st10999/2/3/4' s from dual union all
  3  select 'Fa234st/2/3/4' from dual union all
  4  select '23499/2/3/4' from dual
  5  )
  6  select regexp_replace(s,'^(.*[^0-9/])*([0-9]*)/.*$','\2') r
  7  from t
  8  /

R
-----------------------------------------------------------------------------------------------------------------------------
10999
<null>
23499

Best regards

Maxim

deleted

Message was edited by:
Volder

Hello all,

Is this cheating? ;)

SELECT regexp_substr(regexp_substr('Fa123st10999/2/3/4', '[[:digit:]]+/'),'[[:digit:]]+')
FROM dual

Dan

it is not cheating - it is double use of regular expression functions.

To use regexp only once - the OP can run depending on his/her needs one of the following:

SQL> with t as (
  2      select 'Fa234st10999/2/3/4' s from dual union all
  3      select 'Fa234st/2/3/4' from dual union all
  4      select '23499/2/3/4' from dual
  5      )
  6      select s,
  7             regexp_replace(s, '([[:digit:]]+)/.*|.', '\1') s1,
  8             regexp_replace(s, '([[:digit:]]*)/.*|.', '\1') s2
  9        from t
 10  /

S                  S1          S2
------------------ ----------- ----------
Fa234st10999/2/3/4 10999       10999
Fa234st/2/3/4      2           
23499/2/3/4        23499       23499

SQL> 

Hello all,

Thank you for all your replies. The one that best suites my needs is:

select RegExp_Replace('Fa234st10999/2/3/4','^.*?([0-9]+)/.*$','\1') from dual

which says (for other people just getting into re):

Find the string

which starts from the begging of the string ^ followed by any character . zero or more times * zero or one times ? followed by a group () of digits [0-9] one or more times + followed by a / and followed by a character . zero or more times * till the end of the row $.
Take this string and replace it with the first group found \1.

I also chose this response cause it was shorter than others...

Thank you all again,

Not to take away from Aketi's solution, I was kinda fond of Volder's solution myself.

I also chose this response cause it was shorter than others...

If it's simply a short regex that you are after, then working with Volder's solution, you could rewrite it as:

with t as (
      select 'Fa234st10999/2/3/4' s from dual union all
      select 'Fa234st/2/3/4' from dual union all
      select '23499/2/3/4' from dual
      )
      select s,
             regexp_replace(s, '(\d+)/.*|.', '\1') s1
        from t
/