Split string not working for following query in Oracle-CodePudding

I have following query, Which I am trying to split by "Name:". The output will be inserted into table as follows. But it doesn't work as expected.

jsab
sim
karksd,ad

create or replace procedure p_ins (par_string in varchar2) is
    begin
    insert into TBL_TEST (Name)
    select trim(regexp_substr(par_string, '[^Name:] ', 1, level))
       from dual
         connect by level <= regexp_count(par_string, 'Name:')   1;
  end;
  
  exec p_ins('Name:jsab,Name:sim,Name:karksd,ad');

CodePudding user response：

create table tbl_test (name varchar2(40));

create or replace procedure p_ins (par_string in varchar2) is
    begin
    insert into TBL_TEST (Name)
      select  regexp_substr(par_string,
                  '(Name:)?(.*?)(,Name:|$)', 1, level, null, 2)
      from    dual
      connect by level <= regexp_count(par_string, 'Name:');
  end;
/ 

exec p_ins('Name:jsab,Name:sim,Name:karksd,ad')

select * from tbl_test;


NAME                                    
----------------------------------------
jsab
sim
karksd,ad

NOTES

There are several mistakes in your code, in addition to the regular expression itself (which is clearly incorrect).

The PL/SQL code - the procedure - must be ended with a forward slash; it's missing from what you posted.

The count of names: it should equal the number of occurrences of 'Name:', why are you adding 1? (Answer: because you copied the answer from somewhere and modified it without understanding it).

exec is a SQL*Plus command; as such, it does not require a semicolon at the end. Semicolon is needed only for SQL statements and in PL/SQL code. Some IDE's will ignore the semicolon, but others will throw an error on it.

There is no need to trim the tokens from your string - at least not in the example string you used. Do you expect optional spaces in your strings?

The solution you were trying to adapt to your case works only when the separator is a single character, like comma. It doesn't work in your case. Do you understand what [^Name:] means in a regular expression? I'll bet the answer is "no", otherwise you wouldn't have tried that in your solution.