Postgresql replace + Examples

In this PostgreSQL tutorial, we will learn about the “Postgresql replace() function” to replace the string or character of columns or rows. The following topics will be covered in this tutorial.

  • Postgresql replace
  • Postgresql replace null with empty string
  • Postgresql replace null with 0
  • Postgresql replace first occurrence
  • Postgresql replace multiple characters
  • Postgresql replace first character in string
  • Postgresql replace string in all rows
  • Postgresql replace special characters

Postgresql replace

In Postgresql, when we want to search and replace a string in a column with a new string such as replacing outdated phone numbers, broken URLs, and spelling mistakes in the database.

To search and replace all occurrences of a string with a new string, we use the REPLACE() function

The syntax of REPLACE function is given below.

REPLACE(source, old_string, new_string );

Where,

  • source: It is the string where we want to replace.
  • old_string: It is the string that we want to search and replace.
  • new_string: It is the string that will replace the old_string.

We can also replace the string or character manually or without using replace function.

Let’s understand with an example.

SELECT REPLACE('United Kindom',' ','');

The above code will replace the space between the string United Kindom, where the source is ‘United Kingdom’, old_string is space (‘ ‘) and new_string is nothing (”).

Check the output of the code is given below.

Postgresql replace
Postgresql replace

In the above output, we can see that the space is removed from the string United Kindom.

Also, check: Postgresql REGEXP_REPLACE Function

Postgresql replace null with empty string

In Postgresql, we can replace the null with an empty string, the null is the blank value of the column’s field.

The description of the table emp_info is given below.

description of emp_info
description of emp_info

In the above table, we look at id number 7 where the employee address is null.

Let’s replace the null value with an empty string.

UPDATE emp_info
SET address = ''
WHERE address IS NULL;
SELECT * FROM emp_info;

In the above code, we are replacing the null value without using the replace function.

The output of the above code is given below.

Postgresql replace null with empty string
Postgresql replace null with empty string

In the above code, look at the column address, it has changed from [null] to nothing or empty string or showing nothing.

Read: PostgreSQL Min With Examples

Postgresql replace null with 0

In Postgresql, the null value can also be replaced with the 0 (zero) value, so here we will use the same example as we have used in the above sub-section.

Let’s see other null values in table emp_info.

Postgresql column with null value
Postgresql column with null value

In the above table, we can see the salary_$ column of the employee name Robert is null.

Let’s replace the null value with 0 (zero).

UPDATE emp_info
SET salary_$ = 0
WHERE salary_$ IS Null;
SELECT * FROM emp_info;

The output of the above code is given below.

Postgresql replace null with 0
Postgresql replace null with 0

In the above output, we can the null value replaced by 0 in column salary_$.

Read: PostgreSQL group by with examples

Postgresql replace first ooccurrence

In Postgresql, we can replace the first occurrence of any character or word using the regexp_replace() function instead of replace() function. “Why replace function can’t do that?” because replace is a standard SQL function like other RDBMS.

Now run the below query to replace the first occurrence of the character.

SELECT regexp_replace('I am the database developer','a','aa');

In the above code, first, we have provided the source as ‘I am the database developer’, and the character that we want to match or replace is ‘a’. At last, is a word ‘aa’ that is placed in the place of the character ‘a’.

The output of the above code is given below.

Postgresql replace first occurence
Postgresql replace first occurrence

In the above output, a is replaced by the word ‘aa’ but it replaced only the first occurrence of ‘a’, there is also another character ‘a’ in a sentence.

Read: Postgresql Having Clause

Postgresql replace multiple characters

In Postgresql, there is no inbuilt function to replace multiple characters neither replace nor regexp_replace function can do that, so we will create a function that will replace the multiple characters.

Here we will create the two functions quote_ and multi_char_replace.

Create the quote_ function to quote the characters that are interpreted as special in the regular expression. This function will be used within multi_char_replace.

CREATE FUNCTION quote_(text) RETURNS text AS $$
  select regexp_replace($1, '([\[\]\\\^\$\.\|\?\*\+\(\)])', '\\\1', 'g');
$$ language sql strict immutable;
Quote_ function
Quote_ function

Now create the second function multi_char_replace using the below code.

CREATE FUNCTION multi_char_replace(str text,match_replace jsonb)
RETURNS text
as $$
DECLARE
 rx text;
 s_left text;
 s_tail text;
 res text:='';
BEGIN
 select string_agg(quote_(term), '|' )
 from jsonb_object_keys(match_replace) as x(term)
   where term <> ''
 into rx;

 if (coalesce(rx, '') = '') then
  
   return str;
 end if;

 rx := concat('^(.*?)(', rx, ')(.*)$'); 

 loop
   s_tail := str;
   select 
       concat(matches[1],match_replace->>matches[2]),
       matches[3]
    from
      regexp_matches(str, rx, 'g') as matches
    into s_left, str;
    
   exit when s_left is null;
   res := res || s_left;

 end loop;
Multi_char_replace function
Multi_char_replace function

Let’s call the multi_char_replace to replace multiple characters.

SELECT multi_char_replace('I am the IT person',
             '{"a":"ai", "e":"i", "on":"ion"}'::jsonb);

The output of the above code is given below.

Postgresql replace multiple characters
Postgresql replace multiple characters

Read: PostgreSQL Loop Examples

Postgresql replace first character in string

In Postgresql, the replace function can not replace the first string or character of any sentence or word instead we will use the regexp_replace function.

Let’s run the below code to replace the first character in string.

SELECT regexp_replace('data is everywhere', '^.', 'day');

In the above code, we have provided the source as ‘data is everywhere, and where ‘^.’ represents the first character in a string, ‘day’ is the replacement string.

The output of the above code is given below.

Postgresql replace first character in string
Postgresql replace first character in string

Read: Postgres date range

Postgresql replace string in all rows

In Postgresql, the replace function can replace strings in all rows.

Create a table named emp_data.

CREATE TABLE emp_data (emp_name varchar);

Insert the following records.

INSERT INTO emp_data(emp_name)VALUES('John K'),('Garrett K'),('James K');
emp_data table
emp_data table

Let’s replace the character K character in all strings in column emp_name.

SELECT regexp_replace(emp_name,'K','J') FROM emp_data;

The output of the above code is given below.

Postgresql replace string in all rows
Postgresql replace string in all rows

Read: PostgreSQL Update + Examples

Postgresql replace special characters

In Postgresql, we can replace the special characters as well such as @, #, $ using the replace function.

Let’s replace the email containing a special character.

SELECT replace('Jhon@gmail.com','@','at');

In the above code, we are replacing the @ character in the email with the word at.

The output of the above code is given below.

Postgresql replace special characters
Postgresql replace special characters

Also, take a look at some more tutorials on PostgreSQL.

So, in this tutorial, we have learned about “Postgresql replace” and covered the following topics.

  • Postgresql replace
  • Postgresql replace null with empty string
  • Postgresql replace null with 0
  • Postgresql replace first occurrence
  • Postgresql replace multiple characters
  • Postgresql replace first character in string
  • Postgresql replace string in all rows
  • Postgresql replace special characters