CODEWITHSUNDEEP
sqlplsql
neha
neha

Reputation: 61

need to replace string in plsql

I have a string having %t at one or more places present for id.

I am passing arguments to a function and it should replace the %t with the arguments given to function for that id and return replaced string. 

function test (id in ,value1 in ,value2 in ,value 3 default null....)

select test (1,sql,plsql) from dual should return 'I am working on sql and plsql' for I am working on %t and %t.

Pseudo code (not sure if it is correct) - not sure how to replace p||v_occur to p1, p2 ...

fnc_test(pid IN NUMBER,p1 IN VARCHAR2,p2 IN VARCHAR2..p5 in varchar2 default null) 
RETURN VARCHAR2
  v_out VARCHAR2(4000);
  v_occur NUMBER; 
select (length(string) - length(replace(string,'%t',null)) )/2 
  into v_occur from table where id=pid;

For i in 1..v_occur LOOP 
  v_out := v_out + v_out; 
  SELECT REPLACE(SUBSTR(string, 1,instr(UPPER(string),UPPER('%t'),1)+1),UPPER('%t'), p||v_occur) 
    into v_out FROM table where id=id; 

  RETURN v_out; 
END LOOP;

Upvotes: 1

Views: 1624

Answers (2)

psaraj12
psaraj12

Reputation: 5072

I have adapted the solution from @Littlefoot and made it dynamic as per your requirement where in future you may add additional parameters you just need to add them in the l_params select statement with ',' delimiter

        create or replace function f_test
        (par_id in number,
       par_1 in varchar2 default null,
       par_2 in varchar2 default null,
        par_3 in varchar2 default null,
        par_4 in varchar2 default null,
        par_5 in varchar2 default null
      )
     return varchar2
      is
       l_str  varchar2(200);
       l_no_of_params number;
       l_params varchar2(32767);
     begin
      select string into l_str from dyn_table where id = par_id;

      select regexp_count(l_str, '%t') into l_no_of_params from dual;

      select par_1||','||par_2||','||par_3||','||par_4||','||par_5 into l_params from dual; 

      for i in 1 .. l_no_of_params loop
      for j in 1..l_no_of_params loop
       l_str := regexp_replace(l_str, '%t', regexp_substr(l_params,'[^,]+',1,j), i, 1);
       end loop;

      end loop;
       return l_Str;
     end;

Use the below function for replacement of nulls

    create or replace function f_test
    (par_id in number,
   par_1 in varchar2 default null,
   par_2 in varchar2 default null,
    par_3 in varchar2 default null,
    par_4 in varchar2 default null,
    par_5 in varchar2 default null
  )
 return varchar2
  is
   l_str  varchar2(200);
   l_no_of_params number;
   l_params varchar2(32767);
 begin
  select string into l_str from dyn_table where id = par_id;

  select regexp_count(l_str, '%t') into l_no_of_params from dual;

  select replace(','||par_1||','||par_2||','||par_3||','||par_4||','||par_5||',',',,',',null,') into l_params from dual;

  l_params:=ltrim(rtrim(l_params,','),',');

  for i in 1 .. l_no_of_params loop
  for j in 1..l_no_of_params loop
   l_str := regexp_replace(l_str, '%t', trim(regexp_substr(l_params,'[^,]+',1,j)), i, 1);
   end loop;

  end loop;
   return l_Str;
 end;

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 143083

As there's a limited number of %t strings you can have, there's no need to make it more complex than it should be - consecutive regexp_replace calls make it simple enough, easy to understand.

Test table first:

SQL> create table test (id, string) as
  2    (select 1, 'I am working on %t and %t' from dual union all
  3     select 2, 'Try %t next time'          from dual union all
  4     select 3, 'My %t and my %t are %t %t' from dual
  5    );

Table created.

Function:

SQL> create or replace function f_test
  2    (par_id in number,
  3     par_1 in varchar2 default null,
  4     par_2 in varchar2 default null,
  5     par_3 in varchar2 default null,
  6     par_4 in varchar2 default null
  7    )
  8  return varchar2
  9  is
 10    l_str  varchar2(200);
 11  begin
 12    select string into l_str from test where id = par_id;
 13    for i in 1 .. regexp_count(l_str, '%t') loop
 14      l_str := regexp_replace(l_str, '%t', par_1, 1, 1);
 15      l_str := regexp_replace(l_str, '%t', par_2, 2, 1);
 16      l_str := regexp_replace(l_str, '%t', par_3, 3, 1);
 17      l_str := regexp_replace(l_str, '%t', par_4, 4, 1);
 18    end loop;
 19    return l_Str;
 20  end;
 21  /

Function created.

Testing:

SQL> select f_test(1, 'SQL', 'PL/SQL') result             from dual union all
  2  select f_test(2, 'harder')                           from dual union all
  3  select f_test(3, 'dog', 'cat', 'complete', 'idiots') from dual;

RESULT
------------------------------------------------------------
I am working on SQL and PL/SQL
Try harder next time
My dog and my cat are complete idiots

SQL>

Upvotes: 2

Related Questions