Insert a space character before and after a specific string, yet preserve string, in one update SQL-CodePudding

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

CodePudding user response：

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.