how to update portion of the varchar type data

From: Norman Dunbar <Norman.Dunbar_at_lfs.co.uk>
Date: Thu, 28 Nov 2002 10:36:34 -0000
Message-ID: <E2F6A70FE45242488C865C3BC1245DA702FC0D94@lnewton.leeds.lfs.co.uk>

Morning,

You cannot update part of a field, you have to update the whole filed, so try this :

update objtable
set objectcode = substr(objectcode,1,3) || '123' || substr(objectcode, 7, 3)
where substr(objectcode,1,3) = '00A';

alternatively, since you know that the first 3 chars are going to be
'00A' (from the where clause) you can remove one substr as follows :

update objtable
set objectcode = 00A123' || substr(objectcode, 7, 3) where substr(objectcode,1,3) = '00A';

Also, the length field of the substr can be any old value - so if it is longer than 3 it makes no difference :

update objtable
set objectcode = 00A123' || substr(objectcode, 7, 999) where substr(objectcode,1,3) = '00A';

Enjoy !

Cheers,
Norman.

---

Norman Dunbar
Database/Unix administrator
Lynx Financial Systems Ltd.
mailto:Norman.Dunbar_at_LFS.co.uk

Tel: 0113 289 6265
Fax: 0113 289 3146
URL: http://www.Lynx-FS.com
-------------------------------------

-----Original Message-----
From: hkgirlygirl_at_aol.com (Hkgirlygirl) [mailto:hkgirlygirl_at_aol.com] Posted At: Thursday, November 28, 2002 2:25 AM Posted To: server
Conversation: how to update portion of the varchar type data Subject: how to update portion of the varchar type data

I have a table called OBJTABLE with two columns,

   ID number(10)
   OBJECTCODE VARCHAR(200) and I would like to update a portion of the OBJECTCODE field

e.g. one row in the OBJECTCODE column value is
'00A BCD'

and I like to update col 4-6 with a value

But when I execute the following SQL I got ORA-00927 MIISSIING EQUAL SIGN Update OBJTABLE set substr(OBJECTCODE,4,3) = '123' where substr(OBJECTCODE,1,3) = '00A'

I am using Oracle8i.

Have anyone come across a way to update portion of the varchar data type?
Thanks in advance for your help! Received on Thu Nov 28 2002 - 04:36:34 CST