user1298426 user1298426 - 4 months ago 9
SQL Question

Use replace() function in an update to change more than one sub string of a column

Say I have

student
table with a column
name
.

This
name
column has values 'studentone', 'studenttwo', 'studentthree'

& I want to replace them with 'student1', 'student2', 'student3'.

For single replacement it's quite straight forward:

update student set name = replace(name, 'one', '1')


But what about multiple replacements? Any idea?

Answer

I would just use multiple update statements, but if you absolutely must do it in one statement, just nest the replace calls:

update student set
  name = replace(replace(replace(name, 'one', '1'), 'two', '2'), 'three', '3')

This works because (although inefficient) calls to replace() have no effect if the search term is not found.