Replace a string with another

[Solutions Specialized]

In a table, there is a field/column that has some data, I need to replace a number in the middle of the data that is different from one record to another.

For example "text text 123 text text", text maybe different from one record to another, but the number in the middle is the same, I want to be able to replace the number with another number.

This may not be applied to all records/entries in the table, but to some of them with may be selected based on another column like cat_id.

An SQL statement would be great to work on.

Any help is appreciated.

Thanks,