CODEWITHSUNDEEP
sqlregexoracle-database
Anyul Rivas
Anyul Rivas

Reputation: 735

extracting text from a column using regexp_substr

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

Upvotes: 4

Views: 5536

Answers (4)

Yannis Stereo
Yannis Stereo

Reputation: 121

For multiline XML documents, as we have here, regexp_replace routine could be used but only with 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
/

Upvotes: 1

xionutz2k
xionutz2k

Reputation: 729

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

Upvotes: 2

Ed Gibbs
Ed Gibbs

Reputation: 26333

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 :)

Upvotes: 3

Nick Krasnov
Nick Krasnov

Reputation: 27251

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

Upvotes: 8

Related Questions