Remove/replace special characters from string in PostgreSQL


Sometimes we need to remove special characters from string or columns value. Suppose, we developed an ETL tool that inserting records on a daily basis in the PostgreSQL table from the CSV file. It may be the possibility of junk data insertion in the table, for these types of issue we have to remove the special characters from the columns.
For example, Suppose we have the string value like 'ABC!@#$ XYZ' and "!@#$" is the junk data we want to remove from the string, or that type of data has been inserted into a table column and I want to update the table's columns value to removing special characters. There is a lot of way to fix this issue some of them I am explaining.

Using function regexp_replace()

Using regexp_replace we can remove the special characters from the string or columns. For example, run the following query to remove special characters from the string 'ABC!@#$ XYZ'.      

SELECT regexp_replace('Remove!@#$ Special &*&characters', '[^\w]+','','g');
Result: RemoveSpecialcharacters


You can see here the function removes white space also between the words if you want to keep white space between the words you should use the following query.

SELECT regexp_replace('Remove!@#$ Special &*&characters', '[^\w]+',' ','g');
Result: Remove Special characters


Apply function regexp_replace() on the column or use of regexp_replace() in update statement.

UPDATE Table_Name SET myField = regexp_replace(myField, '[^\w]+',' ','g');
This query will update the myField column removing special characters.


Remove the alphabet characters:

If you want to remove the alphabet characters, you just change the expression '[^\w]+' to '[\w]+'. Run the following query.
  
SELECT regexp_replace('Remove!@#$ Special &*&characters', '[\w]+','','g');
Result: !@#$  &*&


Remove the numeric value from the alphanumeric string:

If you want to remove the numeric values from a string then use the following query
  
SELECT regexp_replace('12Remove 121Special characters12', '[0-9]+','','g');
Result: Remove  Special characters


Remove the alphabet from the alphanumeric string:

If you want to remove alphabet from the alphanumeric string the use the following query.

SELECT regexp_replace('12Remove 121Special characters12', '[a-zA-Z]+','','g');
Result: 12 121 12

NUnit : Introduction

There are several unit test tools in the market, but one of them is NUnit, the most popular tool for doing unit testing. So in this tutorial...