Insert a space character before and after a specific string, yet preserve string, in one update SQL statement (MS SQL)

I require an update statement that will run and insert spaces before and after the string "effective from". So in the following data…

£140,000effective from01-NOV-18
£40,000effective from01-NOV-18
£12,000effective from01-NOV-19

This becomes …

£140,000 effective from 01-NOV-18
£40,000 effective from 01-NOV-18
£12,000 effective from 01-NOV-19

But, it must first detect that the row value has not already been processed, i.e. only insert spaces when the string contains 1x space character

Best Regards

>Solution :

You could use a regular replacement:

UPDATE yourTable
SET data = REPLACE(data, 'effective from', ' effective from ')
WHERE data LIKE '%[0-9]effective from[0-9]%';

This replacement only targets effective from when it is sandwiched in between two numbers.