CODEWITHSUNDEEP
oracle-database
Anders Honoré
Anders Honoré

Reputation: 11

Oracle, regexp_substr - how to match 'string'+regex

I have a column that gives me lines like this:

<?xml version="1.0" encoding="UTF-8"?>
<xSettings>
    <systemPropertyName>BLALBLA</systemPropertyName>
    <minimumAmount>198.00</minimumAmount>
    <closingAmount>198.00</closingAmount>
    <useThisSetting>true</useThisSetting>
    <SystemStep dayAfterPrevious="0">
        <System SystemService="1" minimumAmount="450.00" />
    </SystemStep>
    <SystemStep dayAfterPrevious="8">
        <message />
    </SystemStep>
    <SystemStep dayAfterPrevious="3">
        <block />
    </SystemStep>
    <SystemStep dayAfterPrevious="1">
        <message />
    </SystemStep>
    <SystemStep dayAfterPrevious="7">
        <message />
    </SystemStep>
</xSettings>

All numbers in it are variable and the BLA BLA is variable too. What I want is a select that only gives me

<minimumAmount>198.00</minimumAmount>

though this would be eve better:

198.00

basically, I can't figure out how to use regexp_substr to find a specific string , then return a number just after that may be from 1 to 4 digits and has two decimals after.

Upvotes: 1

Views: 530

Answers (2)

Gurwinder Singh
Gurwinder Singh

Reputation: 39507

XML data should not be queried using regular expressions, but here:

select
  regexp_substr(col, '<minimumAmount>(.*)</minimumAmount>',1,1,null,1) minimum_amount
from your_table;

One other way is to use regexp_replace like this:

select
  regexp_replace(col, '.*<minimumAmount>(.*)</minimumAmount>.*','\1') minimum_amount
from your_table;

Upvotes: 0

MT0
MT0

Reputation: 168232

Do not use regular expressions to parse XML data - use a proper XML parser:

SELECT x.minimumAmount
FROM   your_table t,
       XMLTable(
         '/xSettings',
         PASSING XMLType( t.your_column )
         COLUMNS minimumAmount NUMBER(5,2) PATH './minimumAmount'
       ) x

Or

SELECT TO_NUMBER(
         EXTRACTVALUE( XMLType( your_column ), '/xSettings/minimumAmount' )
       ) AS minimumAmount
FROM   your_table

Upvotes: 2

Related Questions