DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2000


    Dear all,

    I have a sentence like below:


    "I have a %1, it color is %2, and it costs %3"

    This sentence is retrived from a varchar2 column of a table, say if the column is called 'sentence_text',and I would like to replace all %(n) with the input parameters from a procedure. Say for example, the I'm calling the proedure as:

    exec testsentence('car','red','$80000');

    and the sentence retrieved from 'sentence_text' column could have one, two or more %(n), so how a procedure can be written to handle this? Please advise with codes if possible, thanks.

  2. #2
    Join Date
    Aug 2000
    Here is A solution. No doubt wiser people on this board could help you more . . .

    Just pass in two strings:
    1st - the string containing the %1 . . .
    2nd - the comma delimited string of replacement values.

    create or replace function replacecodes (sentence_in varchar2 := NULL, replace_in varchar2 := NULL) return varchar2 as

    stringcount number;
    v_sentence varchar2(1000);
    v_replace varchar2(1000);

    if sentence_in is null or replace_in is null then
    return 'Invalid Input';
    end if;
    v_sentence := sentence_in;
    v_replace := replace_in;
    stringcount := 0;
    while v_replace is not null loop
    stringcount := stringcount + 1;
    select replace(v_sentence,'%'||to_char(stringcount),nvl(rtrim(ltrim(substr(v_replace,1,instr(v_replace,',')-1))),rtrim(ltrim(v_replace)))) into v_sentence from dual;
    if instr(v_replace,',')=0 then
    v_replace := NULL;
    v_replace := substr(v_replace,instr(v_replace,',')+1);
    end if;
    end loop;
    return v_sentence;


    SQL>set serveroutput on
    SQL>exec dbms_output.put_line(replacecodes('%1 bear is the largest, %2 bear is in the middle and %3 bear is smallest','Daddy, Mommy, Baby'));

    You could also use this in a select statement, such as:

    select replacecodes(myvarchar2field,'replacevalue1,replacevalue2') from mytable;

    [Edited by kmesser on 04-17-2001 at 09:24 AM]

  3. #3
    Join Date
    Dec 2000
    Thanks kmesser, I'll try that and let you know how it goes.

  4. #4
    Join Date
    Aug 2000
    For a single line of sql code, you could do something like:

    pct1 varchar2(100) := 'Daddy';
    pct2 varchar2(100) := 'Mommy';
    pct3 varchar2(100) := 'Baby';
    x varchar2(1000);
    select replace(replace(replace('%1 bear is the largest, %2 bear is in the middle and %3 bear is smallest','%1',pct1),'%2',pct2),'%3',pct3) into x from dual;

    I did this from the SQL*PLUS command prompt, so I declared my variables in the anonymous block. Obviously, you could use variables available in the session to which you are connected. The limitation is the number of REPLACE statements you wish to/can nest. I don't know the functional limit, but it gets tougher to manage the code when the nesting goes beyond a certain level.

    Good luck!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Click Here to Expand Forum to Full Width

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.