how to use replace and substring correctly.

[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

[RJSF]

Please, I apologise if I've offended anyone...! Please can someone help with this.

I've figured a way to eliminate the need to specify the defining value but I still can't get the substring function to work so that the change only affects the 8th character in the string??

Can someone please share their expertise with me, many thanks

[madankarmukta]

@RJSF
Hi,

U can implement this by using case when statement.

use case when SUBSTRING(contract,8,1) = 0 the '2'
when SUBSTRING(contract,8,1) = 2 the '4' likewise..

Thanks!