sqlpostgresqlsql-updatepattern-matching

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?

Solution

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