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
|
