Home » SQL & PL/SQL » SQL & PL/SQL » TRANSLATE and REPLACE (9.2.0.6)
TRANSLATE and REPLACE [message #276621] Thu, 25 October 2007 11:27 Go to next message
yerics
Messages: 89
Registered: August 2006
Member
Hi,

I have a table with varchar2 field having values

IDSX***
ICAPA**
TXF4***

I want to replace only the 5th character of the above values to a 'Z'. When I use the translate and replace, the value changes to

IDSXZZZ (Required - IDSXZ**)
ICAPZ** (Required - ICAPZ**) <-- ok
TXF4ZZZ (Required - TXF4Z**)


Regards,
Prasanna
Re: TRANSLATE and REPLACE [message #276625 is a reply to message #276621] Thu, 25 October 2007 11:31 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Why do you need TRANSLATE:

UPDATE my_tab SET my_column = SUBSTR(my_column,1,4) || 'Z' || SUBSTR(my_column,6);
COMMIT;


HTH.
Michael
Re: TRANSLATE and REPLACE [message #276626 is a reply to message #276625] Thu, 25 October 2007 11:37 Go to previous message
yerics
Messages: 89
Registered: August 2006
Member
Thanks a lot.
I have learnt that Sometimes you tend to overlook some simple functions available and go searching for a complex solution to a problem.

Thanks again
Previous Topic: SQL Join
Next Topic: SQL query
Goto Forum:
  


Current Time: Mon Dec 05 04:41:23 CST 2016

Total time taken to generate the page: 0.12217 seconds