In PostgreSQL, the REGEXP_REPLACE function is famous for replacing the existing strings with new strings. It is based on the regular expression that the user writes according to their requirement. This guide explains the REGEXP_REPLACE function through various examples in PostgreSQL:
- Example 1: Use REGEXP_REPLACE Function to Arrange Name
- Example 2: Use REGEXP_REPLACE Function to Remove Extra Spaces
- Example 3: Use REGEXP_REPLACE Function to Remove Alphabets
- Example 4: Use REGEXP_REPLACE Function to Remove Digits
- Example 5: Use REGEXP_REPLACE Function to Replace String in Table
Let's start the journey with the syntax of the REGEXP_REPLACE function in PostgreSQL.
Syntax
REGEXP_REPLACE(input_str, reg_exp, replace_str,[, flags])
The description of the parameters is as follows:
- input_str: the string that needs to be replaced after matching the regular expression.
- reg_exp: represents the regular expression pattern.
- replace_str: represents a string that will be replaced in place of input_str.
- flags: refers to the argument that controls the function's behavior.
Example 1: Use REGEXP_REPLACE Function to Arrange Name
This example will guide you on how to rearrange the strings using the REGEXP_REPLACE() function:
SELECT REGEXP_REPLACE('PostgreSQL Welcome','(.*) (.*)','\2, \1');- The REGEXP_REPLACE function takes two strings: “PostgreSQL” and “Welcome” as arguments.
- After that, regular expression (.*) matches the occurrence.
- \2 replaces the content of the first variable in the second place, while \1 will replace it in the first place:
The output authenticates the working of the REGEXP_REPLACE() function as it succeeded in rearranging the given strings.
Example 2: Use REGEXP_REPLACE Function to Remove Extra Spaces
The REGEXP_REPLACE function can also be used to remove the extra spaces:
SELECT REGEXP_REPLACE('Welcome to database', '( ){2,}', ' ', 'g');
Users can verify the REGEX_REPLACE() function successfully removes the extra spaces and retrieves the output as “Welcome to database”.
Example 3: Use REGEXP_REPLACE Function to Remove Alphabets
Suppose you have to filter the numbers from a string. To do that, the REGEXP_REPLACE() function can be used as follows:
SELECT REGEXP_REPLACE('PostgreSQL51214Database', '[[:alpha:]]','','g');The “:alpha:” expression will remove the alphabet from the existing string:
Users can verify that “PostgreSQL51214Database” has been altered to “51214” using the REGEXP_REPLACE() function.
Example 4: Use REGEXP_REPLACE Function to Remove Digits
For removing digits, you can specify the “:digit:” expression within the REGEXP_REPLACE function:
SELECT REGEXP_REPLACE('PostgreSQL51214Database', '[[:digit:]]','','g');
Users can confirm that “PostgreSQL51214Database” is replaced with the “PostgreSQLDatabase”.
Example 5: Use REGEXP_REPLACE Function to Replace String in Table
In PostgreSQL, the REGEXP_REPLACE() function can be used to replace the existing string with the new one. For better understanding, a table is carried out having some information in it. To fetch the table’s data, we can execute the SELECT query as follows:
SELECT * from candidates;
The output shows that the “candidates” table has one record in it.
Let’s utilize the REGEXP_REPLACE() function to alter the “email” column of the “candidates” table.
SELECT REGEXP_REPLACE(email , 'joe', 'peter') AS "Final" FROM candidates;
In this example, we replaced a string “joe” with the “peter” in the “email” column:
Users can verify that the substring “joe” has been replaced with the “peter” in the existing selected column.
That is all from this guide.
Conclusion
PostgreSQL provides the REGEXP_REPLACE() function to replace an existing string with a new string using regular expressions. The string will be replaced only if a substring satisfies the specified pattern. Users can perform multiple operations using regular expressions, such as rearranging strings, removing alphabets from the strings, removing digits, and so on. This guide has covered the REGEXP_REPLACE() function along with practical implementation in PostgreSQL.
