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');
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.
[^Name:]in your original regex means "All characters except 'N', 'a', 'm', 'e', and ':'". It does not apply any ordering, so "Name:" will be excluded, but so will "N", "me", "aN:em", and all other combinations of those five characters. I don't know of a way to say "don't match this string" in a regular expression - instead, what I've seen done is to use()to make a subexpression out of the string that must exist but which isn't wanted in the result, and then to ignore that subexpression. As you use regex's more you'll become accustomed to this kind of thinking. :-)?operator to the tag, the tag must be in parentheses.