Oracle Regular Expression

User_AS6XD

Input string: 'Chesterton, Mark;#789;#Erbach, Magnus;#786'

Output:Mark(no spaces before Mark)(Basically want to fetch everything between first comma and first semicolon excluding initial whitespaces)

Input String: Phillips, Edward;#744;#Bubba, Matthew;#782;#Mahony, Chloe;#779

Output:Edward

Input String: Corbett, Paul;#784;#Johnson, Ben;#392;#Smith, Adam;#775

Output:Paul

Kindly help.

cormaco

Here is one way:

with input_string(ins) as (
    select 'Chesterton, Mark;#789;#Erbach, Magnus;#786' from dual union all
    select 'Phillips, Edward;#744;#Bubba, Matthew;#782;#Mahony, Chloe;#779' from dual union all
    select 'Corbett, Paul;#784;#Johnson, Ben;#392;#Smith, Adam;#775' from dual
)
select regexp_substr(ins,',\ *([^;]+)',1,1,null,1) regex from input_string

REGEX               
--------------------
Mark                
Edward              
Paul
                

cormaco

Here is one way:

with input_string(ins) as (
    select 'Chesterton, Mark;#789;#Erbach, Magnus;#786' from dual union all
    select 'Phillips, Edward;#744;#Bubba, Matthew;#782;#Mahony, Chloe;#779' from dual union all
    select 'Corbett, Paul;#784;#Johnson, Ben;#392;#Smith, Adam;#775' from dual
)
select regexp_substr(ins,',\ *([^;]+)',1,1,null,1) regex from input_string

REGEX               
--------------------
Mark                
Edward              
Paul