Replace text according to table data

oracle, oracle10g, sql

Solution

try this:

SELECT xmlagg(xmlelement(e, conv)
              ORDER BY l).extract('//text()') .getstringval()
FROM
  (SELECT DISTINCT LEVEL l,
                         substr('DEC', LEVEL, 1) letter
   FROM dual CONNECT BY LEVEL <= length('DEC'))
INNER JOIN jap ON alpha = letter
ORDER BY l ;

Here is a fiddle

Problem

I want to write a program which replaces each specific letter by text according to data given in table. ``` create table jap ( alpha varchar2(1), conv varchar2(5) ); insert into jap(alpha,conv) values ( 'A' , 'ka' ); insert into jap(alpha,conv) values ( 'B' , 'tu' ); insert into jap(alpha,conv) values ( 'C' , 'mi' ); insert into jap(alpha,conv) values ( 'D' , 'te' ); insert into jap(alpha,conv) values ( 'E' , 'ku' ); insert into jap(alpha,conv) values ( 'F' , 'lu' ); insert into jap(alpha,conv) values ( 'G' , 'ji' ); ``` For example, the input `DEC` should result in the output `tekumi`.

Original source