Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Regexp Challenge

572471May 11 2007 — edited May 13 2007
Hello, everyone.

I think it's impossible, but still maybe there would be some thoughts.
Well, the question is:
I have a sentence.
Is it possible to replace particular words with the others?
E.g. sentence 'I want milk'
What I want is: to replace 'I' with 'He',
'want' with 'wants' and 'milk' with 'sugar'.

The main idea is to use only one level of nesting in "replace" function.
I have an example, the first column is some kinda "prototype". It has the logic which I want to implement, but it's not working properly.
The two other columns are working variants, but they are not what I want.
SQL> with t as (select 'I want milk' str from dual)
  2  --
  3  select regexp_replace(str, '(I|want|milk)', decode('\1', 'I', 'He', 'want', 'wants', 'milk', 'sugar', '\1')) smth_like_this,
  4         replace(replace(replace(str,'I', 'He'),'want', 'wants'), 'milk', 'sugar') what_is_needed,
  5         (select str from dual
  6          model
  7           reference r
  8            on  (select 1 rn, 'I' from_str, 'He' to_str from dual union all
  9                select 2, 'want', 'wants' from dual union all
 10                select 3, 'milk', 'sugar' from dual)
 11            dimension by (rn)
 12            measures (from_str f, to_str t)
 13            main m
 14             dimension by (0 dim)
 15             measures (cast(str AS VARCHAR2(4000)) str)
 16             rules iterate (1000) until (presentv(f[iteration_number+1],1,0)=0)
 17              (str[0] = replace(str[0], r.f[iteration_number+1], r.t[iteration_number+1]))
 18           ) from t
 19  /

SMTH_LIKE_THIS WHAT_IS_NEEDED (SELECTSTRFROMDUALMODELREFEREN
-------------- -------------- --------------------------------------------------------------------------------
I want milk    He wants sugar He wants sugar

Comments

mNem 
with t (xmldoc) as 
(
select 
'
<row id="111">
  <c1>10</c1>
  <c1 m="2">11</c1>
  <c1 m="3">12</c1>
  <c1 m="4">13</c1>
  <c1 m="5">14</c1>
  <c1 m="6">15</c1>
  <c2/>
  <c2 m="2">100</c2>
  <c2 m="2" s="2">200</c2>
  <c2 m="4">300</c2>
  <c2 m="6">400</c2>
  <c2 m="6" s="2">700</c2>
  <c2 m="6" s="3">600</c2>
  <c2 m="6" s="4">500</c2>
  <c3 m="6"/>
  <c4 m="6"/>
  <c5 m="6"/>
  <c11>1</c11>
  <c12>297_LUAY1__OFS_BROWSERTC</c12>
  <c13>1810211410</c13>
  <c14>297_LUAY1_OFS_BROWSERTC</c14>
  <c15>PS0010001</c15>
  <c16>1</c16>
</row>
'
from dual
)
select x.* from
t, 
xmltable
(
  '
    let $c1s :=  $t/row/c1 
    let $c2s :=  $t/row/c2
    let $rs  :=
          for $c1 in $c1s
          let $m := $c1/@m
          let $c2s-match-for-c1 := $c2s[@m = $m]
          let $temp-rs := 
                  if(empty($c2s-match-for-c1)) then ()
                  else
                      for $c2 in $c2s-match-for-c1
                      return <r owner = "{$c1}" transaction = "{data($c2)}">{$c2/@m, $c2/@s}</r>
                                
          return if(empty($temp-rs)) then <r owner="{$c1}">{$c1/@m}</r> 
                 else $temp-rs
    return $rs   
  '
  passing xmltype(t.xmldoc) as "t" 
  columns
  ownr        varchar2(10)     path '@owner',
  trans       varchar2(20)     path '@transaction',
  m           varchar2(10)     path '@m',
  s           varchar2(10)     path '@s'
) x
OWNR       TRANS                M          S        
---------- -------------------- ---------- ----------
10                                                   
11         100                  2                    
11         200                  2          2         
12                              3                    
13         300                  4                    
14                              5                    
15         400                  6                    
15         700                  6          2         
15         600                  6          3         
15         500                  6          4         
 10 rows selected
cormaco

That reminds me of this:

generating relational data from xml with multilevel node

and this:

Getting XMLRecord value by position

3230404

many thanx dear.

3230404

again thanx allot @"mNem", u see for the below xml example:

<row id="1003362511">

  <c1>188849</c1>

  <c1 m="2">188616</c1>

  <c1 m="3">188619</c1>

  <c1 m="4">188622</c1>

  <c2>1</c2>

  <c2 m="1" s="2">2</c2>

  <c2 m="1" s="3">3</c2>

  <c2 m="1" s="4">4</c2>

  <c2 m="2">10</c2>

  <c2 m="2" s="2">20</c2>

  <c2 m="3">100</c2>

  <c2 m="4"/>

  <c3 m="4"/>

  <c11>1</c11>

  <c12>305_LUAY1__OFS_BROWSERTC</c12>

  <c13>1810221346</c13>

  <c14>305_LUAY1_OFS_BROWSERTC</c14>

  <c15>PS0010001</c15>

  <c16>1</c16>

</row>

because i have values in c2 m=1 i cant connect it with <c1>188849</c1>, the result for ur SQL is like this:

1003362511 188849

1003362511 188616      10        2

1003362511 188616      20        2      2

1003362511 188619      100      3

1003362511 188622                  4

where  i need it to be like this:

1003362511 188849      1

1003362511 188849      2

1003362511 188849      3

1003362511 188849      4

1003362511 188616      10        2

1003362511 188616      20        2      2

1003362511 188619      100      3

1003362511 188622                  4

can u help plz.....

mNem

what is the possibility of the following xml input .... if positive, please post your expected result set ....

<row id="1003362511">

  <c1>188849</c1>

<c1>1888492</c1>

  <c1 m="2">188616</c1>

  <c1 m="3">188619</c1>

  <c1 m="4">188622</c1>

  <c2>1</c2>

  <c2 m="1" s="2">2</c2>

  <c2 m="1" s="3">3</c2>

  <c2 m="1" s="4">4</c2>

  <c2 m="2">10</c2>

  <c2 m="2" s="2">20</c2>

  <c2 m="3">100</c2>

  <c2 m="4"/>

  <c2 m="4"/> 

  <c11>1</c11>

  <c12>305_LUAY1__OFS_BROWSERTC</c12>

  <c13>1810221346</c13>

  <c14>305_LUAY1_OFS_BROWSERTC</c14>

  <c15>PS0010001</c15>

  <c16>1</c16>

</row>

3230404

from where this <c1>1888492</c1> because its not there in the xml?

unfortunately this is how our application store the xml.

if there is a way to transform the xml to a format easy for u before or during the select it will be ok.

The result set im expecting is same as below:

1003362511 188849      1

1003362511 188849      2

1003362511 188849      3

1003362511 188849      4

1003362511 188616      10        2

1003362511 188616      20        2      2

1003362511 188619      100      3

1003362511 188622                  4

mNem
Answer 
SQL> 
with t (xmldoc) as 
(
select 
'
<row id="1003362511">
  <c1>188849</c1>
  <c1 m="2">188616</c1>
  <c1 m="3">188619</c1>
  <c1 m="4">188622</c1>
  <c2>1</c2>
  <c2 m="1" s="2">2</c2>
  <c2 m="1" s="3">3</c2>
  <c2 m="1" s="4">4</c2>
  <c2 m="2">10</c2>
  <c2 m="2" s="2">20</c2>
  <c2 m="3">100</c2>
  <c2 m="4"/>
  <c2 m="4"/> 
  <c11>1</c11>
  <c12>305_LUAY1__OFS_BROWSERTC</c12>
  <c13>1810221346</c13>
  <c14>305_LUAY1_OFS_BROWSERTC</c14>
  <c15>PS0010001</c15>
  <c16>1</c16>
</row>
'
from dual
)
select x.* from
t, 
xmltable
(
  '
    let $row-id := $t/row/@id
    let $c1s :=  $t/row/c1[@m] 
    let $c2s :=  $t/row/c2
    let $rs  :=
          for $c1 in $c1s
          let $m := $c1/@m
          let $c2s-match-for-c1 := $c2s[@m = $m]
          let $temp-rs := 
                  if(empty($c2s-match-for-c1)) then ()
                  else
                      for $c2 in $c2s-match-for-c1
                      return <r owner = "{$c1}" transaction = "{data($c2)}">{$row-id, $c2/@m, $c2/@s}</r>
                                
          return if(empty($temp-rs)) then <r owner="{$c1}">{$row-id,$c1/@m}</r> 
                 else $temp-rs
                 
    let $c1-without-m := 
         for $i in $t/row/c1[empty(@m)]
         for $c2-without-matching-c1 in $t/row/c2[not(@m = $t/row/c1/@m)]
         return <r owner = "{data($i)}" transaction = "{data($c2-without-matching-c1)}">
                   {$row-id, $c2-without-matching-c1/@m, $c2-without-matching-c1/@s}
                </r>
                
    return ($c1-without-m, $rs)   
  '
  passing xmltype(t.xmldoc) as "t" 
  columns
  id          varchar2(10)     path '@id',
  ownr        varchar2(10)     path '@owner',
  trans       varchar2(20)     path '@transaction',
  m           varchar2(10)     path '@m',
  s           varchar2(10)     path '@s'
) x
ID         OWNR       TRANS                M          S        
---------- ---------- -------------------- ---------- ----------
1003362511 188849     1                                         
1003362511 188849     2                    1          2         
1003362511 188849     3                    1          3         
1003362511 188849     4                    1          4         
1003362511 188616     10                   2                    
1003362511 188616     20                   2          2         
1003362511 188619     100                  3                    
1003362511 188622                          4                    
1003362511 188622                          4                    
 9 rows selected
Marked as Answer by 3230404 · Sep 27 2020
1 person found this helpful
Orcl Apex

Hello,

can you explain your XML logic so that I can better understand it.

mNem

This is what I did in a similar situation ...

match_recognize: Working with pattern-matching for multiple columns

It is probably better if you could spend some time on the problem and then ask SPECIFIC question(s) about things you have trouble following.

Creating a new thread will invite anyone to respond instead of a certain individual (though in this case, the question is about his post).

Any way, why limit it to one person when there are many more experienced guys to benefit from?

Orcl Apex

Huh, Why are you shy to explain logic, frank kulash/soloman/mathguy nerver used to do so.

1 - 10
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jun 10 2007
Added on May 11 2007
15 comments
1,096 views