Q:

Regexp_substr expression

I have problem with my REGEXP expression which I want to loop and every iteration deletes text after slash. My expression looks like this now
REGEXP_SUBSTR('L1161148/1/10', '.*(/)')

I'm getting L1161148/1/ instead of L1161148/1

A:

You said you wanted to loop.  
CAVEAT: Both of these solutions assume there are no NULL list elements (all slashes have a value in between them).
SQL> with tbl(data) as (
     select 'L1161148/1/10' from dual
   )
   select level, nvl(substr(data, 1, instr(data, '/', 1, level)-1), data)  formatted
   from tbl
   connect by level <= regexp_count(data, '/') + 1  -- Loop # of delimiters +1 times
   order by level desc;

     LEVEL FORMATTED
---------- -------------
         3 L1161148/1/10
         2 L1161148/1
         1 L1161148

SQL>

EDIT: To handle multiple rows:
SQL> with tbl(rownbr, col1) as (
        select 1, 'L1161148/1/10/2/34/5/6'           from dual
        union
        select 2, 'ALKDFJV1161148/123/456/789/1/2/3' from dual
   )
   SELECT rownbr, column_value substring_nbr,
          nvl(substr(col1, 1, instr(col1, '/', 1, column_value)-1), col1) formatted
        FROM tbl,
             TABLE(
               CAST(
                 MULTISET(SELECT LEVEL
                             FROM dual
                             CONNECT BY LEVEL <= REGEXP_COUNT(col1, '/')+1
                         ) AS sys.OdciNumberList
               )
             )
   order by rownbr, substring_nbr desc
   ;

    ROWNBR SUBSTRING_NBR FORMATTED
---------- ------------- --------------------------------
         1             7 L1161148/1/10/2/34/5/6
         1             6 L1161148/1/10/2/34/5
         1             5 L1161148/1/10/2/34
         1             4 L1161148/1/10/2
         1             3 L1161148/1/10
         1             2 L1161148/1
         1             1 L1161148
         2             7 ALKDFJV1161148/123/456/789/1/2/3
         2             6 ALKDFJV1161148/123/456/789/1/2
         2             5 ALKDFJV1161148/123/456/789/1
         2             4 ALKDFJV1161148/123/456/789
         2             3 ALKDFJV1161148/123/456
         2             2 ALKDFJV1161148/123
         2             1 ALKDFJV1161148

14 rows selected.

SQL>

