Mobile/Samsung/S22 ultra

expected result: Samsung

Nike/Life style/Clothing/Bottom/Short

exptected result:Bottom

I Tried to use the regex pattern

[^.*] \/

I only get the last folder name not the last second folder name

S22 ultra

Short

How to get last second folder using regex? Do I need to count there's how many delimiters (/) inside the string?

Primary language to get the regex pattern: Oracle

Can the pattern be used in different languages (javacscript/asp.net)?

CodePudding user response：

How to get last second folder using regex?

If it doesn't have to be regex & as it is about Oracle, maybe you should also consider faster (if there are many rows you're dealing with) substr instr option.

Based on your sample data, query might look like this:

SQL> with test (col) as
  2    (select 'Mobile/Samsung/S22 ultra'              from dual union all
  3     select 'Nike/Life style/Clothing/Bottom/Short' from dual
  4    )
  5  select col,
  6    substr(col, instr(col, '/', -1, 2)   1,
  7                instr(col, '/', -1, 1) - instr(col, '/', -1, 2) - 1
  8          ) result
  9  from test;

COL                                   RESULT
------------------------------------- -------------------------------------
Mobile/Samsung/S22 ultra              Samsung
Nike/Life style/Clothing/Bottom/Short Bottom

SQL>

• line #6 instr searches for the 2nd slash appearance in that string, searching from the last position backwards
• line #7 instr then subtracts position of the last slash and position of the second to last slash position (that's the length of the substring you're looking for)
• 1 (line #6) and -1 (line #7) are here to skip slashes themselves

Depending on real data you have, this might need to be adjusted, but - as I said, for sample data you posted, that's "it"

CodePudding user response：

You may use occurrence parameter (the fourth) of regexp_substr function to extract Nth match. If you want to do this in reversed order (Nth last), you need to subtract match number from the total number of slashes which may be obtained by regexp_count.

Example is below. I've used local function declaration just to avoid retyping of the same code.

with function get_group(
  str varchar2,
  match int,
  from_end int default 0
) return varchar2
as
  pos int := 0;
begin
  pos := match;
  if from_end = 1 then
    pos := 2   regexp_count(str, '/') - match;
  end if;
  
  return regexp_substr(
    str,
    '[^/] ',
    1,
    pos
  );
end;

a(a) as (
  select *
  from sys.odcivarchar2list(
    'Mobile/Samsung/S22 ultra',
    'Nike/Life style/Clothing/Bottom/Short'
  )
)

select
  a.*,
  get_group(a, 2) as second,
  get_group(a, 2, 1) as second_last
from a

A                                     | SECOND     | SECOND_LAST
:------------------------------------ | :--------- | :----------
Mobile/Samsung/S22 ultra              | Samsung    | Samsung    
Nike/Life style/Clothing/Bottom/Short | Life style | Bottom     

db<>fiddle here