UPDATE a substring within a field without changing the rest 1

[cdck]

I need to run an update that changes only a few letters/numbers in the middle of a field without changing the rest of the field.

For example, I need to have the query locate all instances of "Blue" in the field Color, then look at the size field and look for the string ".1M". This could be in the middle of a statement like "Height = .1M, width = 3M". I then need it to change only the .1M to .125M. How do I get it to change only this substring, and not the surrounding string?

I am starting with a query like this to locate the records, I need to know how to write the Set section:

[tt]
UPDATE products
SET Size = ???.125M???
WHERE Color = 'Blue' AND Size LIKE '*.1M*';
[/tt]

Thanks,

Cheryl dc Kern

 

[VRoscioli]

How about this?

UPDATE products
SET Size = Replace([Size],".1M",".125M")
WHERE Color = 'Blue' AND Size LIKE '*.1M*';

-V

 

[cdck]

Thank you! I'm so used to applications where I process the data after it's been pulled, that I keep forgetting you do have some functions available in the middle of a query.

Cheryl dc Kern