Extract 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 I don’t know how to use this function and cannot access the oracle documentation.

I'm trying something like

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

No results. Any help would be greatly appreciated.

Answer 1

An easier way would be to use the extractvalue function to extract the value of 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

In fact, REGEXP_REPLACE will work best for this. If you put part of the search expression in parentheses, you can refer to it in the third parameter "replace-with" - the first such expression is \1 , the second is \2 , etc. Until \9 (you cannot do more than 9).

For your requirement, try the following:

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

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

Answer 3

 SELECT regexp_replace( regexp_substr(field, '<valorTasa>[0-9\.]+</valorTasa>'), '<valorTasa>([0-9\.]+)</valorTasa>', '\1') from dual; 

Answer 4

For multi-line XML documents, as we have here, the regexp_replace procedure can be used, but only with the correct match_parameter = mn :

 with t1(col) as( select '<tasa> <parametros> <parametro> <nombre>ea</nombre> <valor>35</valor> </parametro> </parametros> <valorTasa>3.15</valorTasa> </tasa>' from dual ) select REGEXP_REPLACE(col, '^.*<valorTasa>(.*)</valorTasa>.*$', '\1', 1, 0, 'mn') as res from t1 /