sql - Pattern Matching and Replacement -

let's have table this:

text_col --------------- apple, pear apple/orange, pear~pear|pear kiwi banana pear\kiwi

i want change pear in column watermelon.

all can think of select rows have pear, , update pear watermelon 1 one.

is there better/cleaner way it?

i trying create plpgsql trigger function achieve this.

what replace function?

function

replace(string text, text, text)

description

replace occurrences in string of substring substring to

sample

replace('abcdefabcdef', 'cd', 'xx') abxxefabxxef

for code simple update:

update table t set text_col = replace( text_col, 'pear', 'watermelon' );

updated due igor romanchenko suggestion:

update table t  set text_col = replace( text_col, 'pear', 'watermelon' ) text_col '%pear%';

Error