I have values in table like (a,b,c,d,e) and I want to remove any value as mentioned below.
suppose I want to remove b then comma(,) should move i.e (a,c,d,e). Please help.
Add a comment
|
3 Answers
Assuming that all your letters are separated by a single comma, you may use a combination of replace and trim functions.
--Test data
with t(s,r) AS
(
select 'a,b,c,d,e', 'b' from dual union all
select 'a,b,c,d,e', 'e' from dual union all
select 'a,b,c,d,e', 'a' from dual union all
select 'a,b,c,d,e', 'c' from dual union all
select 'a,b,c,d,e', 'd' from dual union all
select 'a,bc,c,d,e', 'bc' from dual union all
select 'ad,bc,c,d,ef','ef' from dual union all
select 'ad,bc,c,d,ef','ad' from dual
)
select s, r as to_remove,
trim ( both ',' from replace(','||s||',', ','||r||',' ,',') ) removed
from t;
S TO_REMOVE REMOVED
a,b,c,d,e b a,c,d,e
a,b,c,d,e e a,b,c,d
a,b,c,d,e a b,c,d,e
a,b,c,d,e c a,b,d,e
a,b,c,d,e d a,b,c,e
a,bc,c,d,e bc a,c,d,e
ad,bc,c,d,ef ef ad,bc,c,d
ad,bc,c,d,ef ad bc,c,d,ef
Comments
You can use either replace or regex_replace to achieve what you expect in plsql.
Select replace(MyTable.MyColumn, 'from value', 'to value') as NewValue from MyTable
1 Comment
Kaushik Nayak
How does this answer the question? The OP wants not just to replace a letter, but the associated commas too. I'm not sure why would someone upvote.
I borrowed code from Kaushik Nayak and added the regexp_replace mentioned by Khatibzadeh.
with t(s,r) AS
(
select 'a,b,c,d,e', 'b' from dual union all
select 'a,b,c,d,e', 'e' from dual union all
select 'a,b,c,d,e', 'a' from dual union all
select 'a,b,c,d,e', 'c' from dual union all
select 'a,b,c,d,e', 'd' from dual
)
select
trim ( both ',' from replace(','||s||',', ','||r||',' ,',') ) trim_replace,
regexp_replace(s, '^' || r || ',|,' || r || '$|,' || r, '') regexp_replace
from t
