sql replace function that matches whole words

Question

I wish to replace some text within a field, so i have the following statement:

UPDATE INVENTORY
SET INV_DESCRIPTION = REPLACE(INV_DESCRIPTION, '5 ml', '5ml (1/6oz)')

The problem lies in the fact that this statement will replace strings such as '5 ml' '15 ml' '150 ml' etc, with the replacement string. I wish for this function to match the whole word and just look for '5 ml'

Answer 1

You could add a WHERE clause which should get you pretty close:

...Your Current Query...
WHERE INV_DESCRIPTION LIKE '5ml%'
OR INV_DESCRIPTION LIKE '% 5ml%'

Which will only update records that start with 5ml or have 5ml with a space before it, which would exclude 15ml or 25ml, etc.

This is assuming SQL Server.