Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2007
    Posts
    64

    Lightbulb Replacing first 4 characters in a string

    Hi ,

    Is there any function in Oracle which replaces the characters in a given index .

    Ex: i have a string 'this is this' . I want to replace first occurrence of
    'this' in above sentence .

    I tried using REPLACE function, but this function replaces ll the occurrences .

    Please suggest me hot to do this . I need this function to update a row data of a table

    Thanks in advance
    Sush

  2. #2
    Join Date
    Oct 2009
    Posts
    1
    create or replace function replace_left(
    p_input in varchar2, --character to be replaced
    p_index in number, --starting index to replace with
    p_replace_with in varchar2) --character to replace
    return varchar2
    is

    v_input varchar(50) := p_input;
    v_index number(2) := p_index;
    v_replace_with varchar(50) := p_replace_with;
    v_result varchar(50) := '';

    begin

    v_result := substr( v_input, v_index + 1, length( v_input ) - v_index );
    v_result := v_replace_with || v_result;

    return v_result;

    end;


    ------------------------------
    to use this function:

    select replace_left('this is this', 4, 'xxxx') from dual (output: xxxx is this)
    select replace_left('this is this', 6, 'nnn') from dual (output: nnns this)

    note: this has no error checking
    Last edited by jackjack; 10-29-09 at 07:04.

  3. #3
    Join Date
    Aug 2007
    Posts
    64
    Thanks a lot Jack...

    My requirement is to replace the string content from index say 2 to 4. More precisely i want to replace substring of a string

    EX : i have a string 'test is test' .
    In the above string i have to replace the content in between the index 2 and 6 .

    Is there any in-built function for getting this .

  4. #4
    Join Date
    Nov 2003
    Posts
    2,821
    Quote Originally Posted by sushma007 View Post
    Thanks a lot Jack...

    My requirement is to replace the string content from index say 2 to 4. More precisely i want to replace substring of a string

    EX : i have a string 'test is test' .
    In the above string i have to replace the content in between the index 2 and 6 .

    Is there any in-built function for getting this .
    I don't really understand your question.

    First you say you want to replace based on the index, then you say you want to replace a certain sequence.

    Which one is true?

    If you really want to replace based on the index, something like the following would work:
    Code:
    UPDATE the_table 
       SET the_column = substr(the_column, 1, 2) || 'new_value' || substr(the_column, 6);

Posting Permissions

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