Postgres set varchar field to regular expression of itself
===

I'm trying to normalise a data field by removing a fairly common postfix. I've got as far as using the **substring()** function in postgres, but can't quite get it to work. For example, if I want to strip the postfix 'xyz' from any values that have it;

    UPDATE my_table SET my_field=substring(my_field from '#"%#"xyz' for '#');

But this is having some weird effects that I cant pin down. Any thoughts? Many thanks as always.