Tuesday 13 March 2012

Per project requirement,we need to replace a single column data which has multiple tag within it with the derived values. Requirement is that replacement should happen in single statement instead of multiple statement

create table test_tab (test varchar2(500))

insert into test_tab values ('<AA>'||'<BB>'||'<CC>')

DECLARE
  xx   VARCHAR2 (2000);
  aa   VARCHAR2 (10)   := '11';
  bb   VARCHAR2 (10)   := '22';
  cc   VARCHAR2 (10)   := '33';
BEGIN
  SELECT TEST
    INTO xx
    FROM test_tab;

  xx    := REPLACE (xx, '<AA>', aa);
  xx    := REPLACE (xx, '<BB>', bb);
  xx    := REPLACE (xx, '<CC>', cc);
  DBMS_OUTPUT.put_line (xx);
END;


Here we are replacing the tag <AA>,<BB>,<CC> with the variables aa,bb,cc in
xx variable using replace command but the statement fires three times.
Our requirement is it has to happen in single statement.
We have the below statement which fulfill our requirement
xx     := REPLACE (REPLACE (REPLACE (xx, '<AA>', aa), '<BB>', bb), '<CC>',
cc);
but I would like to know if there is any alternative statement for this?

If any one knows how this can be achieved please suggest. Any suggestion
with this regard will be of great help!!!
Solutions:
select replace(replace(replace(('<AA>
'||'<BB>'||'<CC>'),'<AA>','aa'),'<BB>'
,'bb'),'<CC>','cc')
from dual;

No comments:

Post a Comment