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

Nick Krasnov · Accepted Answer · 2013-07-17 19:34:23Z

8

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

answered Jul 17, 2013 at 19:34

Nick Krasnov

27.3k6 gold badges66 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ed Gibbs Over a year ago

+1 Nicholas - nice! I plowed ahead with a regex answer and this is a lot slicker :)

Ed Gibbs · Accepted Answer · 2013-07-17 19:35:05Z

3

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

answered Jul 17, 2013 at 19:35

Ed Gibbs

26.4k4 gold badges50 silver badges75 bronze badges

Comments

xionutz2k · Accepted Answer · 2013-07-17 19:35:23Z

2

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

answered Jul 17, 2013 at 19:35

xionutz2k

7293 silver badges9 bronze badges

1 Comment

Anyul Rivas Over a year ago

A simple replace would do the trick in this place. Thanks for your suggestion.

Yannis Stereo · Accepted Answer · 2016-12-23 15:49:13Z

1

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
/

answered Dec 23, 2016 at 15:49

Yannis Stereo

1211 silver badge4 bronze badges

Collectives™ on Stack Overflow

extracting text from a column using regexp_substr

4 Answers 4

1 Comment

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related