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.

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.

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.

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.

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.

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.

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;

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;

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.

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.

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');

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.

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.

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

• Postgresql date to string
• Postgresql date comparison
• Postgresql escape single quote
• PostgreSQL list users
• PostgreSQL Length
• PostgreSQL list databases

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