In PostgreSQL, to remove a particular character from a string we will be using the REPLACE() function. This function can replace all occurrences of a specified substring with another substring, and if you want to remove a character, you can replace it with an empty string. Other Function to remove a specific substring from string is Translate() function Lets see example of how to
- Remove a particular character from string in PostgreSQL using REPLACE() function
- Removing a particular character from string in PostgreSQL using TRANSLATE() function
Remove character from string using REPLACE() function
Syntax
REPLACE(string, from_substring, to_substring)
- string: The original string or column.
- from_substring: The substring (or character) to be removed.
- to_substring: The substring to replace with (in this case, an empty string).
Examples
Example 1: Removing a Specific Character from a String – REPLACE()
To remove all occurrences of the character ‘e’ from the string ‘PostgreSQL’:
SELECT REPLACE('PostgreSQL', 'e', '') AS modified_string;
Output:
Example 2: Removing a Specific Character from a Column – REPLACE()
We will be using summerfruits table.
Removing a particular character from string in PostgreSQL is accomplished by using REPLACE() function as shown below
select *, REPLACE(name,'e','') as New_Name from summerfruits
Character ‘e’ is removed from Colour column. So the resultant table will be
Output:
Remove character from string using TRANSLATE() function
Syntax:
- string: The original string or column to be processed.
- from_set: A set of characters to be replaced.
- to_set: A set of characters to replace the characters in from_set. (replace with “” to remove a character)
Examples
Example 1: Removing a Specific Character from a String
To remove all occurrences of the character ‘e’ from the string ‘PostgreSQL’:
SELECT TRANSLATE('PostgreSQL', 'e', '') AS modified_string;
Output:
Example 2: Removing a Specific Character from a Column – TRANSLATE()
We will be using summerfruits table.
Removing a particular character from string in postgresql is accomplished by using translate() function as shown below
select *,translate(name,'e','') as New_Name from summerfruits
Character ‘e’ is removed from Colour column. So the resultant table will be
Output:
