Home » SQL & PL/SQL » SQL & PL/SQL » REPLACE Function (Oracle,12c,Unix)
REPLACE Function [message #666211] Fri, 20 October 2017 02:11 Go to next message
pstanand
Messages: 133
Registered: February 2005
Location: Chennai,India
Senior Member
Hi I request your help in resolving a replace string issue.
I have column called ckex_text which is varchar2(6000).
In which the 735th position I'm updating with a value PEN.
I used replace function but it is updating other empty places with PEN along with the 735th position.
I would like to update only the 735th position alone.

Could you please help me.

thanks.
Re: REPLACE Function [message #666212 is a reply to message #666211] Fri, 20 October 2017 02:30 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
What is the SQL statement you are running?
Re: REPLACE Function [message #666213 is a reply to message #666211] Fri, 20 October 2017 02:52 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Read the manual REPLACE "returns char with every occurrence of search_string replaced with replacement_string.".

Use REGEXP_REPLACE.

Re: REPLACE Function [message #666214 is a reply to message #666213] Fri, 20 October 2017 03:00 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Or, since the position seems to drive this, substr and || may be simpler and faster.
Re: REPLACE Function [message #666215 is a reply to message #666214] Fri, 20 October 2017 06:27 Go to previous messageGo to next message
pstanand
Messages: 133
Registered: February 2005
Location: Chennai,India
Senior Member
Hi,
I used the below query.
select replace(ckex_text1,substr(ckex_text1,735),'ABC'||substr(ckex_text1,738)) from text_tbl;
Re: REPLACE Function [message #666217 is a reply to message #666215] Fri, 20 October 2017 07:36 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Replace is completely unnecessary there, this does the same* and is a lot easier to understand:
select substr(ckex_text1,1, 735)||'ABC'||substr(ckex_text1,738) from text_tbl



* Unless the chars from position 735 onwards also appear earlier in the string in their entirety. I doubt that's true.
Re: REPLACE Function [message #666220 is a reply to message #666217] Fri, 20 October 2017 08:37 Go to previous message
pstanand
Messages: 133
Registered: February 2005
Location: Chennai,India
Senior Member
Thank you very much it works well.
Previous Topic: DBTime Zone&Off Set
Next Topic: virtual private database
Goto Forum:
  


Current Time: Thu Mar 28 13:37:59 CDT 2024