In PostgreSQL, we can use the replace() function to replace a given substring within a string with another substring.

The function replaces all occurrences of the substring. If the substring doesn’t exist in the string, then the original string is returned unchanged.

Example

Here’s an example to demonstrate:

SELECT replace( 'Meet my cat', 'cat', 'tiger' );

Result:

Meet my tiger

So we can see that the first argument is the original string, the second argument is the substring to replace, and the third string is the replacement text.

As mentioned, all occurrences are replaced, so if the substring occurs multiple times, all of them are replaced. The following example demonstrates this:

SELECT replace( 'My cat ate your cat on the catamaran', 'cat', 'tiger' );

Result:

My tiger ate your tiger on the tigeramaran

Non Existent Substring

If the second argument doesn’t appear in the first, then the first argument is returned unchanged:

SELECT replace( 'Meet my dog', 'cat', 'tiger' );

Result:

Meet my dog

Null Arguments

If any of the arguments is null, then null is returned:

SELECT 
    replace( null, 'cat', 'tiger' ) AS "1",
    replace( 'Meet my cat', null, 'tiger' ) AS "2",
    replace( 'Meet my cat', 'cat', null ) AS "3";

Result:

  1   |  2   |  3   
------+------+------
 null | null | null