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'

which database software are you using? In Oracle regexp_replace would accomplish what you want. — Samuel, Jun 27 '11 at 18:15
What does the whole field (INV_DESCRIPTION) you are trying to update look like? Is it consistent? — Simon, Jun 27 '11 at 18:17
Is this for BusinessVision? AccPac uses Pervasive too, but... — OMG Ponies, Jun 27 '11 at 18:23
@OMG - Pervasive tools also use it, unfortunately. I use some of their other tools but not the SQL implementation. — JNK, Jun 27 '11 at 18:24

score 3 · Accepted Answer · answered Jun 27 '11 at 18:16

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.

sql replace function that matches whole words

1 Answers1

Linked