Forum Stats

  • 3,757,264 Users
  • 2,251,216 Discussions
  • 7,869,781 Comments

Discussions

Oracle Regular Expression

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.

Tagged:

Best Answer

  • cormaco
    cormaco Member Posts: 1,667 Bronze Crown
    Accepted Answer

    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
                    
    


    User_AS6XD

Answers

  • cormaco
    cormaco Member Posts: 1,667 Bronze Crown
    Accepted Answer

    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
                    
    


    User_AS6XD