Search code examples
sqlpervasive

sql replace function that matches whole words

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'

Solution

  • 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.