Dynamically replace data in a oracle field with SQL

Question

I have a varchar column test_data with data in csv format in a table temp_table1. Example Data >

,1,2,3,
,14,18,
,3,4,

for each numerical there is a mapping string like 1="ABC",2="XYX" , 3="PQR" on another database mapping table temp_table2

Is there any way to display the data by replacing each numerical with its mapping using a single sql query??

eg: **,1,2,3**, as **ABC,XYZ,PQR**

I have tried using decode,regexp_replace but cannot find the solution.

Database : Oracle 11g.

Do ***not*** store comma separated values in a single column. Normalize your model and your problem is reduced to a simple (and efficient) `JOIN` operation. — , Aug 26 '14 at 12:11
@a_horse_with_no_name But this field is used in this way at many places. Is there any way to replace by using SQL? — mc20, Aug 26 '14 at 12:15
possible duplicate of ["Reverse GROUP\_CONCAT" in MySQL?](http://stackoverflow.com/questions/17308669/reverse-group-concat-in-mysql) — TJ-, Aug 26 '14 at 12:18

score 1 · Accepted Answer · answered Aug 26 '14 at 12:20

There is a way to do this, albeit a bit yucky. You really should use a junction table and fix the database structure, if you have any control over this.

The method is to expand the data using an inefficient join and then use listagg() to bring the results back together. The result is something like this:

select td.id, listagg(tt.val, ',') within group (order by td.id)
from test_data td left join
     temp_table2 tt
     on td.col like '%,' || tt.col || '%,'
group by td.id;

Dynamically replace data in a oracle field with SQL

1 Answers1