extracting text from a column using regexp_substr

Question

I have a table with a varchar column with data like this:

"<tasa>
  <parametros>
    <parametro>
      <nombre>ea</nombre>
      <valor>35</valor>
    </parametro>
  </parametros>
  <valorTasa>3.15</valorTasa>
</tasa>"

I need to be able to extract the value between the valorTasa tags, but don't know how to use the function and can't access oracle documentation.

I'm trying something like

select regexp_substr(field, '<valorTasa>[0-9]{0-3}</valorTasa') from dual;

With no results. Any help would be greatly appreciated

Answer 1

More simple way would be using extractvalue function to extract the value of the node.

-- sample of data
SQL> with t1(col) as(
  2    select '<tasa>
  3              <parametros>
  4                <parametro>
  5                  <nombre>ea</nombre>
  6                  <valor>35</valor>
  7                </parametro>
  8             </parametros>
  9             <valorTasa>3.15</valorTasa>
 10           </tasa>'
 11      from dual
 12  )
 13  select extractvalue(xmltype(col), '/tasa/valorTasa') as res
 14    from t1
 15  /

RES
-------
3.15

Answer 2

Actually REGEXP_REPLACE will work best for this. If you put a part of the search expression in parentheses you can refer to it in the third "replace-with" parameter - the first such expression is \1, the second is \2, and so on up to \9 (you can't do more than 9).

For your requirement, try this:

SELECT REGEXP_REPLACE(myXMLCol, '^.*<valorTasa>(.*)</valorTasa>.*$', '\1') FROM myTable
                                               ^^^^                   ^^

The part in the parentheses above - (.*) maps to \1. The Oracle REGEXP_REPLACE docs explain this better than I can :)