Home » SQL & PL/SQL » SQL & PL/SQL » replace function (oracle 9i)
replace function [message #296621] Mon, 28 January 2008 07:37 Go to next message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
hello

sir i want to replace 24 by 'C'.

SQL> ED
Wrote file afiedt.buf

  1  SELECT VC_ITEM_CODE FROM MAKESS.MST_ITEM
  2* WHERE VC_ITEM_CODE LIKE '24TPXXCTXX00075900200000'
SQL> /

VC_ITEM_CODE
------------------------------
24TPXXCTXX00075900200000
24TPXXCTXX00075900200000
24TPXXCTXX00075900200000



please tell me how could i replace.

waiting for your reply

thanking you
Re: replace function [message #296626 is a reply to message #296621] Mon, 28 January 2008 07:46 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Show us what you have tried first. Replace and Translate functions may be of help.
Re: replace function [message #296633 is a reply to message #296621] Mon, 28 January 2008 08:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
REPLACE

Regards
Michel
Re: replace function [message #296647 is a reply to message #296621] Mon, 28 January 2008 08:53 Go to previous messageGo to next message
sudhaputtagunta
Messages: 7
Registered: January 2007
Junior Member
Try this


SELECT replace(VC_ITEM_CODE,substr(VC_ITEM_CODE,1,2),'c')
FROM MAKESS.MST_ITEM
WHERE VC_ITEM_CODE LIKE '24TPXXCTXX00075900200000'
Re: replace function [message #296652 is a reply to message #296647] Mon, 28 January 2008 09:34 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
sudhaputtagunta wrote on Mon, 28 January 2008 15:53
Try this


SELECT replace(VC_ITEM_CODE,substr(VC_ITEM_CODE,1,2),'c')
FROM MAKESS.MST_ITEM
WHERE VC_ITEM_CODE LIKE '24TPXXCTXX00075900200000'


Would this not be much easier?
select 'C'||substr(vc_item_code, 3)
from ...

It does exactly the same
Re: replace function [message #296680 is a reply to message #296652] Mon, 28 January 2008 11:49 Go to previous message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Or
SELECT REPLACE('24TPXXCTXX00075900200000', '24', 'c') result FROM dual
or
SELECT REGEXP_REPLACE('24TPXXCTXX0007590020000024', '24', 'c', 1, 1) result FROM dual;
Previous Topic: Adding 2 SUM Functions from two tables
Next Topic: using COUNT for long list of records possible?
Goto Forum:
  


Current Time: Tue Dec 06 08:39:32 CST 2016

Total time taken to generate the page: 0.09723 seconds