Oracle Regexp_ help to do a replace on string

Question

I have a field that has several name values seperated by $;, like below:

$;James$;Paul$;

I have a function available on the names that allows me to identify data from a different table i.e. email address. However the function only works on a single name value. So I need to do a substring to get each name value and then run a replace command that runs the function to pull out their email address. I want to get the following responses James@emailserver.com, Paul@emailserver.com.

So my question is: how can I run a regexp_replace command to identify the names and then split them out to run the replace?

Answer 1

Something like this:

with
     name_list ( id, str ) as (
       select 1, '$;James$;Paul$;'      from dual union all
       select 2, '$;Jane$;Emily$;Ann$;' from dual
     )
select id, substr(str, instr(str, '$;', 1, level) + 2,
              instr(str, '$;', 1, level+1) - instr(str, '$;', 1, level) - 2) as name
from   name_list
connect by level <= regexp_count(str, '\$;') - 1
       and prior id = id
       and prior sys_guid() is not null
;

ID   NAME
--   -----
 1   James
 1   Paul
 2   Jane
 2   Emily
 2   Ann

And then you can use this for your comparisons.

Alternatively, if you need to see if the name James is in your input string (and assuming James is the value in a column name_col):

... where name_list.str like '$;' || name_col || '$;'

and you don't need to split the string anymore. The concatenations are necessary, though, because you don't want Anne in the name list to match the name Ann in the column.

Answer 2

This function helps me to split a varchar:

function f_split_string( p_string varchar2, p_separator varchar2, p_pos number) return varchar2
is
v_string varchar2(100);
begin
     select partition into v_string from
         (select rownum as n , partition from
             (select regexp_substr(p_string, '[^'||p_separator||']+', 1, level) partition from dual
             connect by regexp_substr(p_string, '[^'||p_separator||']+', 1, level) is not null)
         ) t1
     where t1.n = p_pos;

     return trim(v_string);
exception
    when others then return 'nfound';
end;

Example call:

/*using the params*/
p_string := '$;James$;Paul$;'; --string to parse
p_separator := '$;'; --separator
p_pos := 1; --position to get
f_split_string(p_string,p_separator,p_pos) /*return 'James'*/

/*if p_pos :=2*/
f_split_string(p_string,p_separator,p_pos) /* return 'Paul' */

/*if p_pos :=3*/
f_split_string(p_string,p_separator,p_pos) /*return 'nfound' */

/*if p_pos :=0*/
f_split_string(p_string,p_separator,p_pos) /* return 'nfound' */