How to Replace Special Characters in Postgres

In this PostgreSQL tutorial, I will show you how to replace special characters in Postgres using the function REPLACE() and REGEXP_REPLACE().

Also, you will understand the function TRANSLATE() besides these two functions to replace the special characters with new string values.

How to Replace Special Characters in Postgres

Sometimes your database contains a piece of information with special characters in a table. These special characters can be (@,^,&,$,*,%,_,#) and sometimes you keep the special character with information purposely.

But sometimes you get these special characters mistakenly or while importing data from other sources into PostgreSQL.

Maybe this kind of unwanted character gets into your table due to a data conversion problem when Postgresql is unable to understand the information. Usually, this kind of information with special characters should not exist in the database because it is meaningless or has no value with that information.

So what should you do, the answer is to remove or replace these characters according to what you want. PostgreSQL has different kinds of functions which remove or replace special characters with new string values.

Let’s take some examples and understand how to remove and replace special characters in PostgreSQL with new string values.

Replace() Function to Replace Special Characters in Postgres

The REPLACE() function of PostgreSQL replaces the old string value with a new string value. It replaces all the string which matches with the substring specified in this function.

If you have specified the special characters within the function REPLACE() that you want to replace or remove, then REPLACE() function will replace all the matching special characters within a string.

The syntax is given below.

REPLACE(actual_string, string_to_replace, new_string)

• actual_string: This is the source string that contains unwanted characters or strings that you want to replace. Also, the actual_string can be a column of the table.
• string_to_replace: It is a string that you want to replace within the source string.
• new_string: This is the new string value that you want at the place of substring (string_to_replace).

Let’s see with an example how to replace the special characters using the REPLACE() function.

Consider a table ‘customers’ with columns ‘customer_id’, ‘name’, ’email’, ‘age’ and ‘registration_date’. This table contains some special characters in the column name and email.

As you can see from the table data, the column name contains special characters, If you look at the customer id equal to 5, the name ‘Jorge@Cooper’ contains a special character (@).

There should be a space between the first name and last name, So use the below command to fix this problem.

UPDATE 
    customers
SET 
    name = REPLACE(name,'@',' ')
WHERE 
    customer_id = 5
RETURNING *;

In the above code part (name = REPLACE(name,’@’,’ ‘)), replace the special character @ with space (‘ ‘). Actually, the code replaces the @ character in the name ‘Jorge@Cooper’ of column ‘name’ with a space and the output is ‘Jorge Cooper’ which is more readable.

This is how to use replace special characters in PostgreSQL using the REPLACE() function.

REGEXP_REPLACE to Replace Special Characters in Postgres

Another function to replace the special characters in PostgreSQL is REGEXP_REPLACE which replaces the specific part of strings with a new string based on the specified regular expression pattern. The regular expression is the characters in sequence.

The syntax of REGEXP_REPLACE is given below.

REGEXP_REPLACE( actual_string, regexp_pattern, new_string )

• actual_string: The source string in which the matched part of the string using the regexp pattern is replaced by new_string.
• regexp_pattern: This is the regular expression pattern to find the part of the string that you want to replace with new_string.
• new_string: It is the new string that replaces the part of the string which is found by regular expression.

Let’s take an example and understand how to use the regexp_replace to replace the special characters.

Use the below command to replace special characters # from the name ‘James@Johnson#’.

UPDATE 
    customers
SET 
    name = regexp_replace(name, '#',' ')
WHERE 
    customer_id = 1
RETURNING *;

The above query replaces the special character # with space within the name ‘James@Johnson#’ and the output is the ‘James@Johnson‘ but it still has the special character @, so to remove it, use the regular pattern as ‘@’ in the regexp_replace() function.

Translate() to Replace Special Characters in Postgres

The TRANSLATE() function replaces the part of a string with a new string based on a specified set. This function performs the same operation as REPLACE() and REGEXP_REPLACE().

The syntax is given below.

TRANSLATE(actual_string, character_set, new_string)

• actual_string: This is the source string where the found set is replaced by the new string.
• character_set: It is a collection of characters as a set and acts as a pattern.
• new_string: The new string which replaces the set found in the actual string.

Let’s check with an example and see how to use the translate() function to replace special characters in PostgreSQL.

Use the below command to replace most of the special characters in the column ‘name’ of the table customers.

UPDATE 
    customers
SET 
    name = translate(name, '_%#^*@$', '      ') from customers;
RETURNING *;

If you look at the output of the above command, the translate() function replaces all the special characters specified in the column ‘name’ using the specified set ‘_%#^*@$’ with a new string which is space in this case.

Now the name of the employee seems meaningful after replacing the special characters from each name.

But you need to understand one thing about the translate() function which is how you specify the new_string.

Let me show you simple examples, suppose you have the string ‘Ma&ria*’ with two special characters ‘&’ and ‘*’.

Use the below command to replace the special characters with the capital letter ‘K’ and ‘J’.

SELECT translate('Ma&ria*', '&*', 'KJ');

Now understand within set ‘&*’ you want to search this pattern but in the string ‘Ma&ria*’ you see the special character exist at different places. The new string is ‘KJ’ and the output is ‘MaKriaJ’.

So what happens here, the translate function considers the characters within the set as individual characters wherever these characters exist in the actual_string, and for each character, there is a different replacement string or character in the new_string.

Conclusion

In this PostgreSQL tutorial, I showed you how to replace special characters with strings using REGEXP_REPLACE() and REPLACE(). Additionally, you learned about the function TRANSLATE() to replace the special characters based on the specified set which is a set of characters.

You may also read:

• PostgreSQL REGEXP_REPLACE Function
• PostgreSQL Date Add + Add days to date in PostgreSQL Examples
• How to Escape Single Quote in PostgreSQL