how to use replace and substring correctly.

Originally Posted by RJSF

Hi, can someone please help me from my pit of despair....

I have a table - "Drawing" with a column "Contract" -varchar, which contains a 19 character string. E.g. 002559-002677-AN-01 or 002334-100385-AN-01

I need to change the 8th character and depending on what the original value is replace it with a different value, e.g 002559-002677-AN-01 the 8th character in this string is "0" and therefore needs to be "2" whereas in this string 002334-100385-AN-01, the 8th character is "2" and needs to be "4".

I have tried the following script:

Update "drawing"
set "contract"= replace(contract,SUBSTRING(contract,8,1),'2')
where "fieldx3" ='2bupdated'

I know that this doesn't define the character value that needs replacing e.g. from the first example above "0", only (I believed) the position, but when I run it it changes all the/any "0"s in the string to "2"

Where am I going wrong and how can I specify the value to be replaced.

I don't really work with, or know sql, just occasionally I get landed with these types of job!

Any help would be fantastic, thankyou
Reuben