If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Oracle > Replacing first 4 characters in a string

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
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 06:04.
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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 .
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Nov 2003
Posts: 2,799
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);
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On