DB2 Update using SUBSTR

[CharlieKamer]

I have a CHAR(8) column in which I would like to change the 4th and 5th positions to '02' whenever they are equal to '01'

Any suggestions?

 

[Hattusas]

If you want all the data to be transformed into format fo yours this query might help you.

Suppose your table schema is Reservat and your table name is
Resehote and the target field name is resehotenote THEN

update reservat.resehote a set a.resehotenote=Concat ( Concat( substr(a.resehotenote,1,3),'02') ,substr(a.resehotenote,6,(length(a.resehotenote)-6)))

if you want this to be performed everytime automatically,
then you will need a trigger.

Here is the trigger of my own :

CREATE TRIGGER NOTECHNGE
AFTER INSERT ON RESERVAT.RESEHOTE
REFERENCING OLD AS OLDT
FOR EACH ROW MODE DB2SQL
update reservat.resehote set resehotenote=Concat ( Concat( substr(resehotenote,1,2),'XX') ,substr(resehotenote,5,(length(resehotenote)-6))) where
<write the primary key of your new record for example>
Salih Sipahi
Software Engineer.
City of Istanbul Turkey
openyourmind77@yahoo.com

 

[gregsimpson]

I'm not anyway near a database this evening, so this is untested, however I would suggest trying something along the lines of

UPDATE YOUR_TABLE
SET SUBSTR(YOUR_COL,4,2) = &quot;02&quot;
WHERE SUBSTR(YOUR_COL,4,2) = &quot;01&quot;


Greg