CODEWITHSUNDEEP
sqloracle-database
Mat
Mat

Reputation: 91

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

Upvotes: 0

Views: 187

Answers (3)

Gary_W
Gary_W

Reputation: 10360

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>

Upvotes: 1

user5683823
user5683823

Reputation:

You are trying to go as far as the last / and then "look back" and retain what was before it. With regular expressions you can do that with a subexpression, like this:

select regexp_substr('L1161148/1/10', '(.*)/.*', 1, 1, null, 1) from dual;

Here, as usual, the first argument "1" means where to start the search, the second "1" means which matching substring to choose, "null" means no special matching modifiers (like case-insensitive matching and such - not needed here), and the last "1" means return the first subexpression - the first thing in parentheses in the "match pattern."

However, regular expressions should only be used when you can't do it with the standard substr and instr (and translate) functions. Here the job is quite easy:

instr(text_string, '/', -1)

will give you the position of the LAST / in text_string (the -1 means find the last occurrence, instead of the first: count from the end of the string). So the whole thing can be written as:

select substr('L1161148/1/10', 1, instr('L1161148/1/10', '/', -1) - 1) from dual;

Edit: In the spirit of Gary_W's solution, here is a generalization to several strings and stripping successive layers from each input string; still not using regular expressions (resulting in slightly faster performance) and using a recursive CTE, available since Oracle version 11; I believe Gary's solution works only from Oracle 12c on.

Query: (I changed Gary's second input string a bit, to make sure the query works properly)

with tbl(item_id, input_str) as (
        select 1, 'L1161148/1/10/2/34/5/6'           from dual union all
        select 2, 'ALKD/FJV11/61148/123/456/789/1/2/3' from dual
     ),
     r (item_id, proc_string, stage) as (
        select item_id, input_str, 0 from tbl
        union all
        select item_id, substr(proc_string, 1, instr(proc_string, '/', -1) - 1), stage + 1
        from r
        where instr(proc_string, '/') > 0
     )
select * from r
order by item_id, stage;

Output:

   ITEM_ID PROC_STRING                                   STAGE
---------- ---------------------------------------- ----------
         1 L1161148/1/10/2/34/5/6                            0
         1 L1161148/1/10/2/34/5                              1
         1 L1161148/1/10/2/34                                2
         1 L1161148/1/10/2                                   3
         1 L1161148/1/10                                     4
         1 L1161148/1                                        5
         1 L1161148                                          6
         2 ALKD/FJV11/61148/123/456/789/1/2/3                0
         2 ALKD/FJV11/61148/123/456/789/1/2                  1
         2 ALKD/FJV11/61148/123/456/789/1                    2
         2 ALKD/FJV11/61148/123/456/789                      3
         2 ALKD/FJV11/61148/123/456                          4
         2 ALKD/FJV11/61148/123                              5
         2 ALKD/FJV11/61148                                  6
         2 ALKD/FJV11                                        7
         2 ALKD                                              8

Upvotes: 1

Aleksej
Aleksej

Reputation: 22949

You can try removing the string after the last slash:

select regexp_replace('L1161148/1/10', '/([^/]*)$', '') from dual

Upvotes: 1

Related Questions