Home » SQL & PL/SQL » SQL & PL/SQL » Replace all substrings by another strings (Ora 11g)
| Replace all substrings by another strings [message #615046] |
Thu, 29 May 2014 13:36  |
 |
slaguila
Messages: 2
Registered: May 2014
Location: United States
|
Junior Member |
|
|
Hello,
I have query that gives me a string columns like 'apple/pineapple/beet/lettuce/apple/spinach/beef'
I have a table that indicates a category:vegetable,fruit or meat.
I want to change each substring by its category. Something like a replace with a select in inside.
I want to get this string on my select:
'fruit/fruit/vegetable/vegetable/fruit/vegetable/meat'
My list its big so I can't use a decode.
Any idea its appreciated!
thanks
[EDITED by LF: fixed topic title typo; was "repalce"]
[Updated on: Thu, 29 May 2014 14:03] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
| Re: Repalce all substrings by another strings [message #615051 is a reply to message #615049] |
Thu, 29 May 2014 14:25  |
 |
Littlefoot
Messages: 21807
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Here's one option; you can follow the execution by selecting from each step so that you'd see what's going on.
SQL> with
2 -- Categories, as you stated
3 category as
4 (select 'apple' vfm, 'fruit' cat from dual union all
5 select 'pineapple', 'fruit' from dual union all
6 select 'beet' , 'vegetable' from dual union all
7 select 'lettuce' , 'vegetable' from dual union all
8 select 'spinach' , 'vegetable' from dual union all
9 select 'beef' , 'meat' from dual
10 ),
11 -- Current input string
12 string as
13 (select 'apple/pineapple/beet/lettuce/apple/spinach/beef' col
14 from dual
15 ),
16 -- split the string into rows
17 just_one as
18 (select level rb, regexp_substr(col, '[^/]+', 1, level) vfm
19 from string
20 connect by regexp_substr(col, '[^/]+', 1, level) is not null
21 ),
22 -- what is an apple? Pineapple? Spinach? Etc.
23 what_is_what as
24 (select j.rb, j.vfm, c.cat
25 from just_one j, category c
26 where j.vfm = c.vfm
27 )
28 -- OK, the result
29 select listagg(w.cat, '/') within group (order by w.rb) result
30 from what_is_what w;
RESULT
--------------------------------------------------------------------------------
fruit/fruit/vegetable/vegetable/fruit/vegetable/meat
SQL> |
|
|
|
|
|
| Re: Repalce all substrings by another strings [message #615064 is a reply to message #615051] |
Thu, 29 May 2014 18:40 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Just for fun using MODEL:
column col format a60
select col
from string s,
category c
model
return updated rows
partition by(s.rowid)
dimension by(row_number() over(order by 1) rn)
measures(cast(col as varchar2(4000)) col,vfm,cat)
rules iterate(1e6) until(vfm[Iteration_Number+1] is null)
(
col[1] = trim(
both '/'
from replace(
'/' || col[1] || '/',
'/' || vfm[Iteration_Number+1] || '/',
'/' || cat[Iteration_Number+1] || '/'
)
)
)
/
COL
-----------------------------------------------------
fruit/fruit/vegetable/vegetable/fruit/vegetable/meat
SQL>
SY.
|
|
|
|
|
|
|
|
| Re: Repalce all substrings by another strings [message #615077 is a reply to message #615064] |
Fri, 30 May 2014 01:23 |
 |
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
And using recursive query:
SQL> with
2 category as
3 (select 'apple' vfm, 'fruit' cat from dual union all
4 select 'pineapple', 'fruit' from dual union all
5 select 'beet' , 'vegetable' from dual union all
6 select 'lettuce' , 'vegetable' from dual union all
7 select 'spinach' , 'vegetable' from dual union all
8 select 'beef' , 'meat' from dual
9 ),
10 string as
11 (select 'apple/pineapple/beet/lettuce/apple/spinach/beef' col
12 from dual
13 ),
14 result (r, col, res, nbelem) as
15 (select 1 r, s.col, cast(c.cat as varchar2(50)) res,
16 length(s.col)-length(replace(s.col,'/',''))+1 nbelem
17 from string s, category c
18 where regexp_substr(s.col, '[^/]+', 1, 1) = c.vfm
19 union all
20 select r.r+1, r.col, r.res||'/'||c.cat, r.nbelem
21 from result r, category c
22 where regexp_substr(r.col, '[^/]+', 1, r.r+1) = c.vfm
23 and r.r < r.nbelem
24 )
25 select res
26 from result
27 where r = nbelem
28 /
RES
----------------------------------------------------------------------
fruit/fruit/vegetable/vegetable/fruit/vegetable/meat
|
|
|
|
| Re: Repalce all substrings by another strings [message #615134 is a reply to message #615077] |
Fri, 30 May 2014 08:49 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Michel Cadot wrote on Fri, 30 May 2014 02:23
And using recursive query:
I know OP never set rules when one of the items doesn't fall into any category. So I compared solutions. Michel's solution returns no rows:
with
category as
(select 'apple' vfm, 'fruit' cat from dual union all
select 'pineapple', 'fruit' from dual union all
select 'beet' , 'vegetable' from dual union all
select 'lettuce' , 'vegetable' from dual union all
select 'spinach' , 'vegetable' from dual union all
-- select 'beef' , 'meat' from dual union all
select 'pine' , 'tree' from dual
),
string as
(select 'apple/pineapple/beet/lettuce/apple/spinach/beef' col
from dual
),
result (r, col, res, nbelem) as
(select 1 r, s.col, cast(c.cat as varchar2(50)) res,
length(s.col)-length(replace(s.col,'/',''))+1 nbelem
from string s, category c
where regexp_substr(s.col, '[^/]+', 1, 1) = c.vfm
union all
select r.r+1, r.col, r.res||'/'||c.cat, r.nbelem
from result r, category c
where regexp_substr(r.col, '[^/]+', 1, r.r+1) = c.vfm
and r.r < r.nbelem
)
select res
from result
where r = nbelem
/
no rows selected
SQL>
Littlefoot's solution removes such items:
with
category as
(select 'apple' vfm, 'fruit' cat from dual union all
select 'pineapple', 'fruit' from dual union all
select 'beet' , 'vegetable' from dual union all
select 'lettuce' , 'vegetable' from dual union all
select 'spinach' , 'vegetable' from dual union all
-- select 'beef' , 'meat' from dual union all
select 'pine' , 'tree' from dual
),
string as
(select 'apple/pineapple/beet/lettuce/apple/spinach/beef' col
from dual
),
just_one as
(select level rb, regexp_substr(col, '[^/]+', 1, level) vfm
from string
connect by regexp_substr(col, '[^/]+', 1, level) is not null
),
-- what is an apple? Pineapple? Spinach? Etc.
what_is_what as
(select j.rb, j.vfm, c.cat
from just_one j, category c
where j.vfm = c.vfm
)
-- OK, the result
select listagg(w.cat, '/') within group (order by w.rb) result
from what_is_what w
/
RESULT
-------------------------------------------------
fruit/fruit/vegetable/vegetable/fruit/vegetable
SQL>
And my solution leaves item uncahnged:
with
category as
(select 'apple' vfm, 'fruit' cat from dual union all
select 'pineapple', 'fruit' from dual union all
select 'beet' , 'vegetable' from dual union all
select 'lettuce' , 'vegetable' from dual union all
select 'spinach' , 'vegetable' from dual union all
-- select 'beef' , 'meat' from dual union all
select 'pine' , 'tree' from dual
),
string as
(select 'apple/pineapple/beet/lettuce/apple/spinach/beef' col
from dual
)
select col
from string s,
category c
model
return updated rows
dimension by(row_number() over(order by 1) rn)
measures(cast(col as varchar2(4000)) col,vfm,cat)
rules iterate(1e6) until(vfm[Iteration_Number+1] is null)
(
col[1] = trim(
both '/'
from replace(
'/' || col[1] || '/',
'/' || vfm[Iteration_Number+1] || '/',
'/' || cat[Iteration_Number+1] || '/'
)
)
)
/
COL
------------------------------------------------------------
fruit/fruit/vegetable/vegetable/fruit/vegetable/beef
SQL>
And, another recursive solution:
with
category as
(select 'apple' vfm, 'fruit' cat from dual union all
select 'pineapple', 'fruit' from dual union all
select 'beet' , 'vegetable' from dual union all
select 'lettuce' , 'vegetable' from dual union all
select 'spinach' , 'vegetable' from dual union all
select 'beef' , 'meat' from dual union all
select 'pine' , 'tree' from dual
),
string as
(select 'apple/pineapple/beet/lettuce/apple/spinach/beef' col
from dual
),
c as (
select c.*,
row_number() over(order by 1) rn,
count(*) over() cnt
from category c
),
r(rn,cnt,col) as
(
select rn,
cnt,
replace('/' || col || '/','/' || vfm || '/','/' || cat || '/') col
from string s,
c
where c.rn = 1
union all
select r.rn + 1,
r.cnt,
replace('/' || col || '/','/' || vfm || '/','/' || cat || '/') col
from r,
c
where c.rn = r.rn + 1
and r.rn <= r.cnt
)
select trim( both '/' from col) col
from r
where rn = cnt
/
COL
----------------------------------------------------
fruit/fruit/vegetable/vegetable/fruit/vegetable/meat
with
category as
(select 'apple' vfm, 'fruit' cat from dual union all
select 'pineapple', 'fruit' from dual union all
select 'beet' , 'vegetable' from dual union all
select 'lettuce' , 'vegetable' from dual union all
select 'spinach' , 'vegetable' from dual union all
-- select 'beef' , 'meat' from dual union all
select 'pine' , 'tree' from dual
),
string as
(select 'apple/pineapple/beet/lettuce/apple/spinach/beef' col
from dual
),
c as (
select c.*,
row_number() over(order by 1) rn,
count(*) over() cnt
from category c
),
r(rn,cnt,col) as
(
select rn,
cnt,
replace('/' || col || '/','/' || vfm || '/','/' || cat || '/') col
from string s,
c
where c.rn = 1
union all
select r.rn + 1,
r.cnt,
replace('/' || col || '/','/' || vfm || '/','/' || cat || '/') col
from r,
c
where c.rn = r.rn + 1
and r.rn <= r.cnt
)
select trim( both '/' from col) col
from r
where rn = cnt
/
COL
-----------------------------------------------------
fruit/fruit/vegetable/vegetable/fruit/vegetable/beef
SQL>
SY.
|
|
|
|
Goto Forum:
Current Time: Thu Apr 18 17:00:33 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
