Search code examples
sqlregexoracle-databaseregexp-substr

Oracle regex to extract string between first pair of < and > brackets

I am have been assigned a task to parse a string (which is essentially in XML format) and I need to extract the name of the first tag in the string

eg: string '<column><data-type>string</data-type>.............' or '<filter><condition>....</condition>...............' or

'......................'

the string keeps changing but I am only interested in the first tag, I would like to get the output like:

  • column,
  • filter,
  • query

i have tried regexp_substr(string,'^<(.+)>',1,1,null,1) and some similer variations but they don't seem to be working cosistently.

Please help.

Solution

  • You are looking for any character between the bounds -- and that includes '>'. So, just exclude the terminating character:

    select regexp_substr(string,'^<([^>]+)>',1,1,null,1)
from (select '<column><data-type>string</data-type>.............' as string from dual union all
      select '<filter><condition>....</condition>...............' from dual
     ) x;