Help with UPDATE statement to a single position

[JEPTALK01]

I am running into problems trying to write SQL to update a single position within a field for throusands of records.

Thousands of records on a table have an incorrect number in a field (UIDY) and I am looking for the most simple way to update them all.

If it were only a single record, I would execute the following:
Update MF_VEND_ADDR_DOC set UIDY = '&4027&7608&7607&1609&VC19&VC19000055&&0&999&M' where UIDY = '&4025&7608&7607&1609&VC19&VC19000055&&0&999&M'

The only difference in those two lines is that I want to change the &4025& to &4027&.

Does anyone know of a way to update thousands of similar records by updating the 5th position in the UIDY field, thus eliminating the need to list out each UIDY value?

 

[r937]

use REPLACE and CONCAT and SUBSTRING string functions

r937.com | rudy.ca

 

[JEPTALK01]

Thanks! I had not used Replace before and that seemed to work:

UPDATE MF_VEND_ADDR_DOC SET UIDY = REPLACE(UIDY,'&4025&','&4027&')

 

[r937]

glad you got it sorted

turns out you didn't want to update only the 5th position, but rather, a particular substring anywhere it occurs

r937.com | rudy.ca