Only replace string in sql when not adjacent to other characters

Question

I'm using MSSQL 2008 to replace the values in certain columns. My current query is:

UPDATE table  
SET name = replace(name, 'old', 'new')

I would like to know if it is possible to only replace 'old' by 'new' when 'old' is not adjacent to other characters. That is: only when it actually occurs as 'old' This would ensure that e.g. 'bold' does not become 'bnew'

Currently I'm retrieving results via JDBC, splitting the string at the delimiter (,) and then checking if the string equals'old'. If so I want to replace with 'new'. It takes a long time to retrieve all the results and send them back, so if this is possible by only sending an SQL-statement containg 'old' & 'new' that would be great!

Records where it should be replaced:

• 'old , blabla blabla, bla'
• 'blabla, old, blabla'
• 'blabla, bla old, blabla'
• 'blabla, blabla, old'
• 'blabla, bla old bla'

Records where it shouldn't be replaced:

• 'blaold, blabla'
• 'blabla, oldbla'

Answer 1

Use spaces in your clauses.

UPDATE [YourTable] SET name = replace(name, ' old ', 'new') WHERE name = ' old '